--------------------------------------------------------
--  DDL for Procedure CA_GETALL_ADVERTISMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_GETALL_ADVERTISMENT" 
(
  OP_OUTOF_ADVER OUT SYS_REFCURSOR,
  OP_SELECT_ADVER OUT SYS_REFCURSOR
)
AS
T_ROW MOBILE_ADVERTISMENT%rowtype;--临时行
T_AFTER_7 NVARCHAR2(20);--七天后的时间

BEGIN
  
      T_AFTER_7:=to_char(SYSDATE + 7,'yy-mm-dd hh24:mi');
    --选择出已经超过7天过期广告
      OPEN OP_OUTOF_ADVER FOR
        SELECT * FROM MOBILE_ADVERTISMENT
        WHERE TO_DATE(MOBILE_ADVERTISMENT.ENDTIME,'yyyy-mm-dd hh24:mi') < TO_DATE(to_char(SYSDATE+7,'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi');
    ----删除过期广告
      LOOP
        FETCH OP_OUTOF_ADVER INTO T_ROW;
        
        BEGIN
          DELETE FROM MOBILE_ADVERTISMENT 
          WHERE MOBILE_ADVERTISMENT."ADVID"=T_ROW."ADVID";
          COMMIT;
        END;
     END LOOP;
  
  --查询
     OPEN OP_OUTOF_ADVER FOR
        SELECT * FROM MOBILE_ADVERTISMENT;
      
END CA_GETALL_ADVERTISMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_GETALL_ADVERTISMENT_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_GETALL_ADVERTISMENT_V2" 
(
OP_OPERATE_CODE OUT NUMBER,
OP_SELECT_ADVER OUT SYS_REFCURSOR,
OP_OUTOFDATE_ADVER OUT NVARCHAR2
)
AS
  
 
BEGIN
  
    OP_OUTOFDATE_ADVER:= CA_FUN_DELETE_OUTOFDATE_AVER();
    OPEN OP_SELECT_ADVER FOR
        SELECT * FROM MOBILE_ADVERTISMENT;
        OP_OPERATE_CODE:=1;
        
EXCEPTION 
WHEN NO_DATA_FOUND THEN
  OP_OPERATE_CODE:=0;
WHEN OTHERS THEN 
  OP_OPERATE_CODE:=-1;
  
END CA_GETALL_ADVERTISMENT_V2;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_DELETE_PLAY_COMMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_DELETE_PLAY_COMMENT" 
(
  IP_RECORD_ID IN NUMBER,--记录ID
  --日志相关
  IP_LOGIN_ID IN NVARCHAR2,--用户登录ID
  IP_USER_IP IN VARCHAR2,--用户IP
  OP_OPERATE_CODE OUT NUMBER--操作响应代码
 
)
AS 
  T_FUN_RESULT NUMBER ;
  DELETE_PERM_EXCEPTION EXCEPTION;--删除权限错误
  T_USER_ID NVARCHAR2(20);
  T_REMARK NVARCHAR2(100);
BEGIN
          --判断用户是否具有删除该记录的权限
          SELECT USER_ID INTO T_USER_ID  FROM CA_PLAY_COMMENT WHERE  RECORD_ID =IP_RECORD_ID ;
          IF T_USER_ID <> IP_LOGIN_ID THEN--登录ID是否与该记录的用户一样
            RAISE DELETE_PERM_EXCEPTION;--抛出异常
          ELSif T_USER_ID = IP_LOGIN_ID THEN
            
            BEGIN
            --删除记录
              
              DELETE FROM CA_PLAY_COMMENT
              WHERE RECORD_ID=IP_RECORD_ID;
              --删除评论对应的回复
              DELETE FROM CA_PLAY_COMMENT
              WHERE PARENT_ID=IP_RECORD_ID;
              
              --记录用户操作
              T_REMARK:='用户['|| IP_LOGIN_ID ||']通过IP['|| IP_USER_IP ||']删除数据表[CA_PLAY_COMMENT]记录ID为['||IP_RECORD_ID ||']的数据,删除[成功].';
              T_FUN_RESULT:=CA_FUN_RECORD_LOG(
                IP_LOGIN_ID,
                1,
                IP_USER_IP,
                T_REMARK,
                1
              );
              
              --提交更改
              COMMIT;
              OP_OPERATE_CODE:=1;
              
            END;
          END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
   
    ROLLBACK;
WHEN DELETE_PERM_EXCEPTION THEN--权限不足
    OP_OPERATE_CODE:=-2;
  
    ROLLBACK;    
WHEN OTHERS THEN
   OP_OPERATE_CODE:=-1;
 
   ROLLBACK;          
      
END CA_PROC_DELETE_PLAY_COMMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_DELETE_TWEET
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_DELETE_TWEET" 
(
  IP_TWEET_TYPE IN NUMBER,--类型
  IP_RECORD_ID IN NUMBER,--记录ID
  --日志相关
  IP_LOGIN_ID IN NVARCHAR2,--用户登录ID
  IP_USER_IP IN VARCHAR2,--用户IP
  OP_OPERATE_CODE OUT NUMBER,--操作响应代码
  OP_OPERATE_EXTRAS1 OUT NVARCHAR2,--返回的额外数据,如果存在图片缩略图，则返回
  OP_OPERATE_EXTRAS2 OUT NVARCHAR2--图片的大图路径
)
AS 
  T_FUN_RESULT NUMBER ;
  DELETE_PERM_EXCEPTION EXCEPTION;--删除权限错误
  T_USER_ID NVARCHAR2(20);
  T_REMARK NVARCHAR2(100);
BEGIN
  --DELETE
  
  --存储变量
  BEGIN
      IF IP_TWEET_TYPE = 1 THEN
        BEGIN
          --删除趣事
          --存储变量
          SELECT 
            USER_ID,IMG_THUMB_PATH,IMG_ORIGIN_PATH INTO T_USER_ID,OP_OPERATE_EXTRAS1,OP_OPERATE_EXTRAS2
           
          FROM CA_PLAY 
          WHERE RECORD_ID = IP_RECORD_ID;
          
          --判断用户是否具有删除该记录的权限
          IF T_USER_ID <> IP_LOGIN_ID THEN--登录ID是否与该记录的用户一样
            RAISE DELETE_PERM_EXCEPTION;--抛出异常
          ELSE
            
            BEGIN
            --删除记录
              
              DELETE FROM CA_PLAY
              WHERE RECORD_ID=IP_RECORD_ID;
              --删除评论
              DELETE FROM CA_PLAY_COMMENT
              WHERE REF_ID =IP_RECORD_ID;
              
              --记录用户操作
              T_REMARK:='用户['|| IP_LOGIN_ID ||']通过IP['|| IP_USER_IP ||']删除数据表[CA_PLAY]记录ID为['||IP_RECORD_ID ||']的数据,删除[成功].';
              T_FUN_RESULT:=CA_FUN_RECORD_LOG(
                IP_LOGIN_ID,
                1,
                IP_USER_IP,
                T_REMARK,
                1
              );
              
              --提交更改
              COMMIT;
              OP_OPERATE_CODE:=1;
              
            END;
          END IF;
          
        END;
      ELSIF IP_TWEET_TYPE = 2 THEN--删除吃喝玩乐
        BEGIN
            --存储变量
            SELECT 
              USER_ID,IMG_CONTENT,ORIGINPATH INTO T_USER_ID,OP_OPERATE_EXTRAS1,OP_OPERATE_EXTRAS2
             
            FROM COMMON_ENTERTAINMENT 
            WHERE RECORD_ID = IP_RECORD_ID;
            
            --判断权限
             IF T_USER_ID <> IP_LOGIN_ID THEN--登录ID是否与该记录的用户一样
                RAISE DELETE_PERM_EXCEPTION;--抛出异常
             ELSE
                  BEGIN
            --删除记录
              
              DELETE FROM COMMON_ENTERTAINMENT
                WHERE RECORD_ID=IP_RECORD_ID;
              --删除转载
              --DELETE FROM COMMON_ENTERTAINMENT
               --WHERE COMMON_REPRINT_ID=IP_RECORD_ID; 
              --删除评论
              DELETE FROM COMMON_ENTERTAINMENT_COMMENT
              WHERE REF_ID =IP_RECORD_ID;
              
              --记录用户操作
              T_REMARK:='用户['|| IP_LOGIN_ID ||']通过IP['|| IP_USER_IP ||']删除数据表[COMMON_ENTERTAINMENT]记录ID为['||IP_RECORD_ID ||']的数据,删除[成功].';
              T_FUN_RESULT:=CA_FUN_RECORD_LOG(
                IP_LOGIN_ID,
                2,
                IP_USER_IP,
                T_REMARK,
                1
              );
              
              --提交更改
              COMMIT;
              OP_OPERATE_CODE:=1;
              
              END;   
             END IF;
        END;
      END IF;
    
    
  END;
  


--异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
    OP_OPERATE_EXTRAS1:='';
    OP_OPERATE_EXTRAS2:='';
    ROLLBACK;
WHEN DELETE_PERM_EXCEPTION THEN--权限不足
    OP_OPERATE_CODE:=-2;
    OP_OPERATE_EXTRAS1:='';
    OP_OPERATE_EXTRAS2:='';
    ROLLBACK;    
WHEN OTHERS THEN
   OP_OPERATE_CODE:=-1;
   OP_OPERATE_EXTRAS1:='';
   OP_OPERATE_EXTRAS2:='';
   ROLLBACK;
END CA_PROC_DELETE_TWEET;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_ENTER_CHECK_TOOGLE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_ENTER_CHECK_TOOGLE" (
  IP_RECORD_ID IN NVARCHAR2,
  IP_CHECK_FLAG IN NUMBER,
  OP_UPDATE_RESULT OUT NUMBER
)
AS
BEGIN
  OP_UPDATE_RESULT:=-1;
  UPDATE COMMON_ENTERTAINMENT 
  SET 
  CHECK_FLAG=IP_CHECK_FLAG
  WHERE RECORD_ID=IP_RECORD_ID;
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
  EXCEPTION 
  WHEN OTHERS THEN
    ROLLBACK;
    OP_UPDATE_RESULT:=-1;
    
END CA_PROC_ENTER_CHECK_TOOGLE;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_ENTER_DETAIL_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_ENTER_DETAIL_V2" 
(
  IP_RECORD_ID IN NUMBER,
  OP_OPERATE_CODE OUT NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
  
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM COMMON_ENTERTAINMENT WHERE RECORD_ID = IP_RECORD_ID;
    OP_OPERATE_CODE :=1;
    
    
EXCEPTION 
WHEN NO_DATA_FOUND THEN
  OP_OPERATE_CODE :=0;
WHEN OTHERS THEN
    OP_OPERATE_CODE :=-1;

END CA_PROC_ENTER_DETAIL_V2;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_FEEDBACK_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_FEEDBACK_DETAIL" 

(
  IP_RECORD_ID IN NUMBER,
  OP_OPERATE_CODE OUT NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
  
BEGIN
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM COMMON_FEEDBACK
  WHERE RECORD_ID = IP_RECORD_ID;
  OP_OPERATE_CODE:=1;
  
EXCEPTION 
WHEN NO_DATA_FOUND THEN
  OP_OPERATE_CODE:=0;
WHEN OTHERS THEN
   OP_OPERATE_CODE:=-1;
   
END CA_PROC_FEEDBACK_DETAIL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_GETENTER_BY_USERID
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_GETENTER_BY_USERID" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  IP_USER_ID IN NVARCHAR2,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
  V_CUR_PAGE NUMBER;
  T_CHECK_OPEN NUMBER(1,0);
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
  ---判断是否开启审核
  T_CHECK_OPEN:=CA_FUN_ENTERCHECK_OPEN();
  IF T_CHECK_OPEN = 0 THEN
  BEGIN
  OPEN OP_SELECT_RESULT
  FOR
  SELECT * 
  FROM
    (
     SELECT "RECORD_ID",
      "USER_ID",
      "TITLE",
      "TEXT_CONTENT",
      "IMG_CONTENT",
      "PUBLISH_DATE",
      "CLICKS",
      "ENTERTAINMENT_TYPE",
      "COMMON_REPRINT_ID",
      "RN"
    FROM
      (SELECT t.*,
        rownum rn
      FROM
        (
          SELECT * FROM v_entertainment WHERE "USER_ID"=IP_USER_ID
          ORDER BY PUBLISH_DATE DESC
        ) t
      )
    )
   WHERE 
        RN >= (V_CUR_PAGE + 1) AND 
        RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
     
    END; 
  ELSIF  T_CHECK_OPEN = 1 THEN
   BEGIN
  OPEN OP_SELECT_RESULT
  FOR
  SELECT * 
  FROM
    (
     SELECT "RECORD_ID",
      "USER_ID",
      "TITLE",
      "TEXT_CONTENT",
      "IMG_CONTENT",
      "PUBLISH_DATE",
      "CLICKS",
      "ENTERTAINMENT_TYPE",
      "COMMON_REPRINT_ID",
      "RN"
    FROM
      (SELECT t.*,
        rownum rn
      FROM
        (
          SELECT * FROM v_entertainment WHERE "USER_ID"=IP_USER_ID AND CHECK_FLAG = 1
          ORDER BY PUBLISH_DATE DESC
        ) t
      )
    )
   WHERE 
        RN >= (V_CUR_PAGE + 1) AND 
        RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
     
    END; 
    END IF;
END CA_PROC_GETENTER_BY_USERID;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_GETENTER_PAGE_ALL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_GETENTER_PAGE_ALL" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
  V_CUR_PAGE NUMBER;
  T_CHECK_OPEN NUMBER(1,0);
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
  ---判断是否开启审核
  T_CHECK_OPEN:=CA_FUN_ENTERCHECK_OPEN();
  IF T_CHECK_OPEN = 0 THEN
  BEGIN
  OPEN OP_SELECT_RESULT
  FOR
  SELECT * 
  FROM
    (
     SELECT "RECORD_ID",
      "USER_ID",
      "TITLE",
      "TEXT_CONTENT",
      "IMG_CONTENT",
      "PUBLISH_DATE",
      "CLICKS",
      "ENTERTAINMENT_TYPE",
      "COMMON_REPRINT_ID",
      "RN"
    FROM
      (SELECT t.*,
        rownum rn
      FROM
        (
          SELECT * FROM v_entertainment 
          ORDER BY PUBLISH_DATE DESC
        ) t
      )
    )
   WHERE 
        RN >= (V_CUR_PAGE + 1) AND 
        RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
     
    END; 
  ELSIF  T_CHECK_OPEN = 1 THEN
   BEGIN
  OPEN OP_SELECT_RESULT
  FOR
  SELECT * 
  FROM
    (
     SELECT "RECORD_ID",
      "USER_ID",
      "TITLE",
      "TEXT_CONTENT",
      "IMG_CONTENT",
      "PUBLISH_DATE",
      "CLICKS",
      "ENTERTAINMENT_TYPE",
      "COMMON_REPRINT_ID",
      "RN"
    FROM
      (SELECT t.*,
        rownum rn
      FROM
        (
          SELECT * FROM v_entertainment WHERE  CHECK_FLAG = 1
          ORDER BY PUBLISH_DATE DESC
        ) t
      )
    )
   WHERE 
        RN >= (V_CUR_PAGE + 1) AND 
        RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
     
    END; 
    END IF;
END CA_PROC_GETENTER_PAGE_ALL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_GET_ADV_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_GET_ADV_DETAIL" (
  IP_RECORD_ID IN NUMBER,
  OP_OPERATE_CODE OUT NUMBER,
  OP_DETAIL_CONTENT OUT SYS_REFCURSOR
)
AS

BEGIN
    OPEN OP_DETAIL_CONTENT FOR
    SELECT *
    FROM  MOBILE_ADVERTISMENT 
    WHERE ADVID =IP_RECORD_ID;
    OP_OPERATE_CODE:=1;
     
EXCEPTION
WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
    --OP_DETAIL_CONTENT:=NULL;
WHEN OTHERS THEN
    OP_OPERATE_CODE:=-1;
    --OP_DETAIL_CONTENT:=NULL;
END CA_PROC_GET_ADV_DETAIL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_GET_PLAY_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_GET_PLAY_DETAIL" 
(
  IP_OBJECT IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_OPERATE_CODE OUT NUMBER
)
AS
BEGIN
  OP_OPERATE_CODE:=0;
 OPEN OP_SELECT_RESULT FOR
  SELECT 
  CA_PLAY."RECORD_ID",
  "CONTENT",
  "IMG_THUMB_PATH",
  "IMG_ORIGIN_PATH",
  "GOOD_COUNT",
  "WORSE_COUNT",
  "FLAG_HIDE",
  "LOCATION",
  "PUB_DATE",
  CA_PLAY."USER_ID",
  "USERHEAD",
        "NICKNAME"
FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
     LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
WHERE CA_PLAY."RECORD_ID" = IP_OBJECT;
OP_OPERATE_CODE:=1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=-1;
    
END CA_PROC_GET_PLAY_DETAIL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_INSERT_ENTERTAINMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_INSERT_ENTERTAINMENT" 
(
  IP_USER_ID IN NVARCHAR2, 
  IP_TITLE IN NVARCHAR2,
  IP_TEXT_CONTENT IN CLOB,
  IP_IMG_CONTENT IN NVARCHAR2,
  IP_ADDRESS IN NVARCHAR2,
  IP_RECOMMANDDER_ID IN NUMBER,
  IP_ENTERTAINMENT_TYPE IN NUMBER,
  IP_COMMON_REPRINT_ID IN NUMBER,
  IP_IP IN VARCHAR2,
  IP_ORIGINPATH IN  NVARCHAR2,
  OP_INSERT_RESULT OUT NUMBER
)
AS
  T_PROCESS_TITLE NVARCHAR2(60);
  T_PROCESS_CONTENT NVARCHAR2(500);
  t_userid common_user.login_id%type;
BEGIN
  OP_INSERT_RESULT:=-1;
  --处理数据
  T_PROCESS_TITLE:= CA_FUN_CHK_SENSITIVE(IP_TITLE);
  T_PROCESS_CONTENT:= CA_FUN_CHK_SENSITIVE(IP_TEXT_CONTENT);
  INSERT INTO COMMON_ENTERTAINMENT
  (
    USER_ID,
    TITLE,
    TEXT_CONTENT,
    IMG_CONTENT,
    PUBLISH_DATE,
    ADDRESS,
    RECOMMANDDER_ID,
    ENTERTAINMENT_TYPE,
    COMMON_REPRINT_ID,
    IP,
    ORIGINPATH
  )
  VALUES
  (
    IP_USER_ID,
    T_PROCESS_TITLE,
    T_PROCESS_CONTENT,
    IP_IMG_CONTENT,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi'),
    IP_ADDRESS,
    IP_RECOMMANDDER_ID,
    IP_ENTERTAINMENT_TYPE,
    IP_COMMON_REPRINT_ID,
    IP_IP,
    IP_ORIGINPATH
  );
  
  
  
  --生成消息通知
  
  COMMIT;
  OP_INSERT_RESULT:=1;
  
  
  EXCEPTION
  
  WHEN OTHERS THEN
    OP_INSERT_RESULT:=-11;
    ROLLBACK;

END CA_PROC_INSERT_ENTERTAINMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_INSERT_ENTER_COMMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_INSERT_ENTER_COMMENT" 
(
  IP_USER_ID IN NVARCHAR2, 
  IP_REF_ID IN NUMBER ,
  IP_IP IN VARCHAR2,
  IP_COMMENT_CONTENT IN NVARCHAR2,
  IP_COMMENT_TYPE IN NUMBER,
  IP_COMMENT_LOCATION IN NVARCHAR2,
  OP_INSERT_RESULT OUT NUMBER
)
AS
  T_PROCESS_COMMENT_CONTENT NVARCHAR2(200);
  
BEGIN
  OP_INSERT_RESULT:=-1;
  --处理数据
  T_PROCESS_COMMENT_CONTENT:= CA_FUN_CHK_SENSITIVE(IP_COMMENT_CONTENT);
    
    INSERT INTO COMMON_ENTERTAINMENT_COMMENT
    (
     USER_ID,
     REF_ID,
     COMMENT_DATE,
     IP,
     COMMENT_CONTENT,
     COMMENT_TYPE,
     COMMENT_LOCATION
    )VALUES(
      IP_USER_ID,
      IP_REF_ID,
      TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi'),
      IP_IP,
      T_PROCESS_COMMENT_CONTENT,
      IP_COMMENT_TYPE,
      IP_COMMENT_LOCATION
    );
  
   COMMIT;
  OP_INSERT_RESULT:=1;  
  EXCEPTION
  WHEN OTHERS THEN
    OP_INSERT_RESULT:=-11;
    ROLLBACK;    
END CA_PROC_INSERT_ENTER_COMMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_INSERT_MOBILE_LOG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_INSERT_MOBILE_LOG" 
(
  IP_CILENT_INFO IN NVARCHAR2,
  IP_LOG_FILE_PATH IN NVARCHAR2,
  IP_DEVICE_TYPE IN NUMBER,
  IP_UPLOAD_IP IN VARCHAR2,
  OP_INSERT_RESULT OUT NUMBER
)
AS

BEGIN
  OP_INSERT_RESULT:=-1;
  
  INSERT INTO CA_MOBILE_LOG(
  CILENTINFO,
  LOGFILE_PATH,
  DEVICE_TYPE,
  UPLOAD_DATE,
  UPLOAD_IP
  )
  VALUES(
    IP_CILENT_INFO,
    IP_LOG_FILE_PATH,
    IP_DEVICE_TYPE,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss'),
    IP_UPLOAD_IP
  );
  OP_INSERT_RESULT:=1;
  COMMIT;
  
  EXCEPTION
  
  WHEN OTHERS THEN
    OP_INSERT_RESULT:=2;
    ROLLBACK;

END CA_PROC_INSERT_MOBILE_LOG;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_INSERT_SENSITIVE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_INSERT_SENSITIVE" 
(
  IP_SENSITIVE_STR IN NVARCHAR2,
  IP_SOURCE IN NVARCHAR2
 )
AS

BEGIN
 
  
  INSERT INTO CA_SENSITIVE(
    SENSITIVE_STR,
    "SOURCE"
  )
  VALUES(
    IP_SENSITIVE_STR,
    IP_SOURCE
  );
   COMMIT;
  
  EXCEPTION
  
  WHEN OTHERS THEN
      ROLLBACK;

END CA_PROC_INSERT_SENSITIVE;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_LOG_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_LOG_DETAIL" 
(
  IP_LOG_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
  
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM CA_MOBILE_LOG WHERE LOG_ID=IP_LOG_ID;
    
END CA_PROC_LOG_DETAIL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PAGE_PLAY
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PAGE_PLAY" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  IP_USER_ID IN NVARCHAR2,
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_OPERATE_CODE OUT NUMBER
)
AS
T_ISNULL NUMBER(1,0);
V_CUR_PAGE NUMBER;
BEGIN
  T_ISNULL:=CA_CHECK_NULL(IP_USER_ID);
  OP_OPERATE_CODE:=0;
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
  IF T_ISNULL = 1 THEN 
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
   
     SELECT 
        "CA_PLAY"."RECORD_ID",
      "CONTENT",
      "IMG_THUMB_PATH",
      "IMG_ORIGIN_PATH",
      "GOOD_COUNT",
      "WORSE_COUNT",
      "FLAG_HIDE",
      "LOCATION",
      "PUB_DATE",
      "USER_ID",
      "USERHEAD",
      "NICKNAME"
    FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
                 LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
    
    ORDER BY PUB_DATE DESC

   ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);

 ELSIF  T_ISNULL = -1 THEN 
 OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
   
 
    SELECT 
    "CA_PLAY"."RECORD_ID",
  "CONTENT",
  "IMG_THUMB_PATH",
  "IMG_ORIGIN_PATH",
  "GOOD_COUNT",
  "WORSE_COUNT",
  "FLAG_HIDE",
  "LOCATION",
  "PUB_DATE",
  CA_PLAY."USER_ID",
  "USERHEAD",
      "NICKNAME"
FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
     LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
WHERE CA_PLAY."USER_ID" = IP_USER_ID
ORDER BY PUB_DATE DESC


   ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
 END IF;
   OP_OPERATE_CODE:=1;
 
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
  WHEN OTHERS THEN
    OP_OPERATE_CODE:=-1;
  
END CA_PROC_PAGE_PLAY;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PAGE_PLAY_COMMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PAGE_PLAY_COMMENT" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  IP_REOCORD_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_OPERATE_CODE OUT NUMBER
)
AS

V_CUR_PAGE NUMBER;
BEGIN
 
  OP_OPERATE_CODE:=0;
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
 
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
   
 
 SELECT 
      CA_PLAY_COMMENT."RECORD_ID",
      "USER_ID",
      "REF_ID",
      "COMMENT_DATE",
      "COMMENT_IP",
      "COMMENT_CONTENT",
      "COMMENT_LOCATION"
     --"COMMON_USER_PROFILE"."USERHEAD",
      --"NICKNAME"
FROM CA_PLAY_COMMENT 
WHERE CA_PLAY_COMMENT.REF_ID=IP_REOCORD_ID
ORDER BY COMMENT_DATE DESC


   ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);

 OP_OPERATE_CODE:=1;
 
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
  WHEN OTHERS THEN
    OP_OPERATE_CODE:=-1;
  
END CA_PROC_PAGE_PLAY_COMMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PAGE_PLAY_COMMENT_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PAGE_PLAY_COMMENT_V2" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  IP_REOCORD_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_OPERATE_CODE OUT NUMBER
)
AS
T_COMMENT_TYPE NUMBER(1);
V_CUR_PAGE NUMBER;
T_ROW CA_H_PLAY_COMMENT%ROWTYPE;
T_USERNAME NVARCHAR2(20);
T_ORIGIN_ID NVARCHAR2(20);
T_ORIGIN_NAME NVARCHAR2(20);
T_USER_HEAD NVARCHAR2(100);
T_ERROR_MSG NVARCHAR2(200);
T_SELECT_RESULT  SYS_REFCURSOR;
BEGIN
 
  OP_OPERATE_CODE:=0;
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  T_COMMENT_TYPE:=0;
 
  OPEN T_SELECT_RESULT FOR
  SELECT * FROM ( 
   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
   SELECT 
      CA_PLAY_COMMENT."RECORD_ID",
      "USER_ID",
      "REF_ID",
      "COMMENT_DATE",
      "COMMENT_IP",
      "COMMENT_CONTENT",
      "COMMENT_LOCATION",
      CA_PLAY_COMMENT."COMMENT_TYPE",
      "PARENT_ID"
     FROM CA_PLAY_COMMENT 
    WHERE CA_PLAY_COMMENT.REF_ID=IP_REOCORD_ID
    ORDER BY COMMENT_DATE DESC
      ) T)
      )
      WHERE
      RN >= (V_CUR_PAGE + 1) AND 
      RN <= (V_CUR_PAGE + IP_PAGE_SIZE);


 
   LOOP
    FETCH T_SELECT_RESULT INTO T_ROW;
     Exit WHEN T_SELECT_RESULT%NOTFOUND; 
     --判断评论类型
     T_COMMENT_TYPE:=T_ROW.COMMENT_TYPE;
     BEGIN
      SELECT 
      "COMMON_USER_PROFILE"."USERHEAD",
      "NICKNAME"
      INTO T_USER_HEAD,T_USERNAME
        
    FROM COMMON_USER_PROFILE
    LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
    WHERE COMMON_USER_PROFILE.LOGIN_ID=T_ROW.USER_ID;
    
    
     
      IF T_COMMENT_TYPE=2 THEN--回复评论
        
        SELECT  COMMON_USER."NICKNAME", CA_PLAY_COMMENT."USER_ID"
            INTO T_ORIGIN_NAME,T_ORIGIN_ID
         FROM CA_PLAY_COMMENT
          left join  COMMON_USER ON CA_PLAY_COMMENT.USER_ID = COMMON_USER.LOGIN_ID 
        WHERE CA_PLAY_COMMENT."RECORD_ID" = T_ROW."PARENT_ID";
        
        INSERT INTO CA_T_PLAY_COMMENTS
        (
        CA_T_PLAY_COMMENTS."RECORD_ID",
        CA_T_PLAY_COMMENTS."USER_ID",
        CA_T_PLAY_COMMENTS."COMMENT_TYPE",
        CA_T_PLAY_COMMENTS."COMMENT_CONTENT",
        CA_T_PLAY_COMMENTS."ORIGIN_ID",
        CA_T_PLAY_COMMENTS."COMMENT_DATE",
        CA_T_PLAY_COMMENTS."USER_NAME",
        CA_T_PLAY_COMMENTS."ORIGIN_USERNAME",
        CA_T_PLAY_COMMENTS."USER_HEAD",
        CA_T_PLAY_COMMENTS."PARENT_ID",
        CA_T_PLAY_COMMENTS."REF_ID"
        )
        VALUES(
        T_ROW.RECORD_ID,
        T_ROW.USER_ID,
        T_ROW.COMMENT_TYPE,
        T_ROW.COMMENT_CONTENT,
        T_ORIGIN_ID,
        T_ROW.COMMENT_DATE,
        T_USERNAME,
        T_ORIGIN_NAME,
        T_USER_HEAD,
        T_ROW."PARENT_ID",
        T_ROW."REF_ID"
        );
      COMMIT;
      ELSIF T_COMMENT_TYPE =1 THEN--评论
        INSERT INTO CA_T_PLAY_COMMENTS
        (
        CA_T_PLAY_COMMENTS."RECORD_ID",
        CA_T_PLAY_COMMENTS."USER_ID",
        CA_T_PLAY_COMMENTS."COMMENT_TYPE",
        CA_T_PLAY_COMMENTS."COMMENT_CONTENT",
        CA_T_PLAY_COMMENTS."ORIGIN_ID",
        CA_T_PLAY_COMMENTS."COMMENT_DATE",
        CA_T_PLAY_COMMENTS."USER_NAME",
        CA_T_PLAY_COMMENTS."ORIGIN_USERNAME",
        CA_T_PLAY_COMMENTS."USER_HEAD",
        CA_T_PLAY_COMMENTS."PARENT_ID",
        CA_T_PLAY_COMMENTS."REF_ID"
        )
        VALUES(
        T_ROW.RECORD_ID,
        T_ROW.USER_ID,
        T_ROW.COMMENT_TYPE,
        T_ROW.COMMENT_CONTENT,
        '',
        T_ROW.COMMENT_DATE,
        T_USERNAME,
        '',
        T_USER_HEAD,
        T_ROW."PARENT_ID",
        T_ROW."REF_ID"
        );
            COMMIT;
      END IF;
     END;
    T_COMMENT_TYPE:=0;
   END LOOP;
   CLOSE T_SELECT_RESULT;
  COMMIT;
--查找全部数据
  OPEN OP_SELECT_RESULT FOR 
    SELECT * FROM CA_T_PLAY_COMMENTS ORDER BY COMMENT_DATE DESC;
   DELETE FROM  CA_T_PLAY_COMMENTS ;
  COMMIT;
 OP_OPERATE_CODE:=1;
 
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
  WHEN OTHERS THEN
    T_ERROR_MSG:=sqlerrm;
    OP_OPERATE_CODE:=-1;
  
 
END CA_PROC_PAGE_PLAY_COMMENT_V2;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PAGE_PLAY_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PAGE_PLAY_V2" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  IP_USER_ID IN NVARCHAR2,
  IP_CATEGORY_TAG IN NUMBER,--1表示获取全部数据,2表示获取自己的数据
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_OPERATE_CODE OUT NUMBER
)
AS
--T_ISNULL NUMBER(1,0);
V_CUR_PAGE NUMBER;
V_BLACK_LIST VARCHAR2(100);
t_msg nvarchar2(200);
dyn_sql varchar2(2000);

BEGIN
  --T_ISNULL:=CA_CHECK_NULL(IP_USER_ID);
  OP_OPERATE_CODE:=0;
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  V_BLACK_LIST:=TRIM(SYSTEM.CA_BLACK_LIST_TAPI.GET_BLACK_LIST(IP_USER_ID,18));
  dyn_sql:='';
  
   --V_BLACK_LIST:='(';
  IF TRIM(V_BLACK_LIST) IS NULL OR TRIM(V_BLACK_LIST) = '' THEN 
    
  
			  IF IP_CATEGORY_TAG = 1 THEN 
			--  OPEN OP_SELECT_RESULT FOR
      dyn_sql:=' SELECT * FROM ( 
			   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
			   
				 SELECT 
					"CA_PLAY"."RECORD_ID",
				  "CONTENT",
				  "IMG_THUMB_PATH",
				  "IMG_ORIGIN_PATH",
				  "GOOD_COUNT",
				  "WORSE_COUNT",
				  "FLAG_HIDE",
				  "LOCATION",
				  "PUB_DATE",
				  "USER_ID",
				  "USERHEAD",
				  "NICKNAME"
				FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
							 LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
				
				ORDER BY PUB_DATE DESC

			   ) T)
			  )
			  WHERE
			  RN >= '||(V_CUR_PAGE + 1)||' AND 
			  RN <= '||(V_CUR_PAGE + IP_PAGE_SIZE)||'';
			 

			 ELSIF  IP_CATEGORY_TAG = 2 THEN 
			-- OPEN OP_SELECT_RESULT FOR
      dyn_sql:='
       SELECT * FROM ( 
			   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
			   
			 
				SELECT 
				"CA_PLAY"."RECORD_ID",
			  "CONTENT",
			  "IMG_THUMB_PATH",
			  "IMG_ORIGIN_PATH",
			  "GOOD_COUNT",
			  "WORSE_COUNT",
			  "FLAG_HIDE",
			  "LOCATION",
			  "PUB_DATE",
			  CA_PLAY."USER_ID",
			  "USERHEAD",
				  "NICKNAME"
			FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
				 LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
			WHERE CA_PLAY."USER_ID" = '||IP_USER_ID||'
			ORDER BY PUB_DATE DESC


			   ) T)
			  )
			  WHERE
			  RN >= '||(V_CUR_PAGE + 1)||' AND 
			  RN <= '||(V_CUR_PAGE + IP_PAGE_SIZE)||'';
			 
			 END IF;
	ELSE 
					  IF IP_CATEGORY_TAG = 1 THEN 
			 
              dyn_sql:='
               SELECT * FROM ( 
			   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
			   
				 SELECT 
					"CA_PLAY"."RECORD_ID",
				  "CONTENT",
				  "IMG_THUMB_PATH",
				  "IMG_ORIGIN_PATH",
				  "GOOD_COUNT",
				  "WORSE_COUNT",
				  "FLAG_HIDE",
				  "LOCATION",
				  "PUB_DATE",
				  "USER_ID",
				  "USERHEAD",
				  "NICKNAME"
				FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
							 LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
				WHERE 	"CA_PLAY"."RECORD_ID" NOT IN ('||V_BLACK_LIST||')
				ORDER BY PUB_DATE DESC

			   ) T)
			  )
			  WHERE
			  RN >= '||(V_CUR_PAGE + 1)||' AND 
			  RN <= '||(V_CUR_PAGE + IP_PAGE_SIZE)||'
              ';
			 
			 ELSIF  IP_CATEGORY_TAG = 2 THEN 
		
         dyn_sql:='
           SELECT * FROM ( 
			   SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
			   
			 
				SELECT 
				"CA_PLAY"."RECORD_ID",
			  "CONTENT",
			  "IMG_THUMB_PATH",
			  "IMG_ORIGIN_PATH",
			  "GOOD_COUNT",
			  "WORSE_COUNT",
			  "FLAG_HIDE",
			  "LOCATION",
			  "PUB_DATE",
			  CA_PLAY."USER_ID",
			  "USERHEAD",
				  "NICKNAME"
			FROM CA_PLAY left join COMMON_USER_PROFILE on CA_PLAY.USER_ID =COMMON_USER_PROFILE.LOGIN_ID
				 LEFT JOIN COMMON_USER ON COMMON_USER_PROFILE.LOGIN_ID = COMMON_USER.LOGIN_ID
			WHERE CA_PLAY."USER_ID" = '||IP_USER_ID||'
			 AND  "CA_PLAY"."RECORD_ID" NOT IN ('||V_BLACK_LIST||')
			ORDER BY PUB_DATE DESC


			   ) T)
			  )
			  WHERE
			  RN >='|| (V_CUR_PAGE + 1)||' AND 
			  RN <= '||(V_CUR_PAGE + IP_PAGE_SIZE)||'
         ';
			
			 END IF;

      
	END IF;	
    
   OPEN OP_SELECT_RESULT FOR dyn_sql;
  --execute immediate dyn_sql;
   OP_OPERATE_CODE:=1;
   
   -- dbms_output.put_line();
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
    t_msg :='{code['||sqlcode||' msg['||sqlerrm||']]}';
  WHEN OTHERS THEN
  t_msg :='{code['||sqlcode||' msg['||sqlerrm||']]}';
    OP_OPERATE_CODE:=-1;
    
END CA_PROC_PAGE_PLAY_V2;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PAGE_WEBUSER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PAGE_WEBUSER" (
    IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_SELECT_CODE OUT NUMBER,
    OP_SUMALL OUT NUMBER )
AS
  V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR
  SELECT DISTINCT *
  FROM
    (SELECT T.*,
      ROWNUM RN
    FROM
      (
      SELECT  
        WEB_ADMINCP_MEMBER.*,
        CA_ROLE.ROLE_NAME
      FROM WEB_ADMINCP_MEMBER,
        
        CA_ROLE
      WHERE
       WEB_ADMINCP_MEMBER.GROUP_ID   = CA_ROLE.ROLE_ID
      ) T
    )
  WHERE RN >= (V_CUR_PAGE + 1)
  AND RN   <= (V_CUR_PAGE + IP_PAGE_SIZE);
  
  SELECT  COUNT(*) INTO OP_SUMALL
      FROM WEB_ADMINCP_MEMBER,
        COMMON_USER,
        CA_ROLE
      WHERE WEB_ADMINCP_MEMBER.ULOGIN_ID=COMMON_USER.LOGIN_ID
      AND WEB_ADMINCP_MEMBER.GROUP_ID   = CA_ROLE.ROLE_ID;
      
    OP_SELECT_CODE:=1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    OP_SELECT_CODE:=0;
  WHEN OTHERS THEN
     OP_SELECT_CODE:=-1;
    
END CA_PROC_PAGE_WEBUSER;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PLAY_COMMENT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PLAY_COMMENT" (
IP_USER_ID IN NVARCHAR2,--用户id
IP_REF_ID IN NUMBER,
IP_PUB_IP IN NVARCHAR2,--IP
IP_LOCATION IN NVARCHAR2,--地址

IP_COMMENT_CONTENT IN NVARCHAR2,
OP_OPERATE_RESULT OUT NUMBER--操作结果
)
AS
T_PROCESS_CONTENT NVARCHAR2(200);
BEGIN
    OP_OPERATE_RESULT:=-1;
    T_PROCESS_CONTENT:=CA_FUN_CHK_SENSITIVE(IP_COMMENT_CONTENT);
    INSERT INTO CA_PLAY_COMMENT
    (
      "USER_ID",
      "REF_ID",
      "COMMENT_DATE",
      "COMMENT_IP",
      "COMMENT_CONTENT",
      "COMMENT_LOCATION"
   
      
    )VALUES
    (
    IP_USER_ID,
    IP_REF_ID,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi'),
    IP_PUB_IP,
    T_PROCESS_CONTENT,
    IP_LOCATION
    );
    
     OP_OPERATE_RESULT:=1;
     COMMIT; 
    
     EXCEPTION 
  WHEN OTHERS THEN
   OP_OPERATE_RESULT:=-1;
   ROLLBACK; 
END CA_PROC_PLAY_COMMENT;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PLAY_COMMENT_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PLAY_COMMENT_V2" (
IP_USER_ID IN NVARCHAR2,--用户id
IP_REF_ID IN NUMBER,
IP_PUB_IP IN NVARCHAR2,--IP
IP_LOCATION IN NVARCHAR2,--地址
IP_COMMENT_TYPE IN NUMBER,
IP_PARENT_ID IN NUMBER,
IP_COMMENT_CONTENT IN NVARCHAR2,
OP_OPERATE_RESULT OUT NUMBER--操作结果
)
AS
T_PROCESS_CONTENT NVARCHAR2(200);
T_USER_ID COMMON_USER.LOGIN_ID%type;
t_user_name common_user.nickname%type;
t_notified_user COMMON_USER.LOGIN_ID%type;
t_notified_name common_user.nickname%type;
t_message nvarchar2(200);
--t_cur_rec_id number(10,0);
BEGIN
    OP_OPERATE_RESULT:=-1;
   -- t_cur_rec_id:=  SEQ_PLAYCOMMENT.nextval ;
    T_PROCESS_CONTENT:=CA_FUN_CHK_SENSITIVE(IP_COMMENT_CONTENT);
    INSERT INTO CA_PLAY_COMMENT
    (
     -- "RECORD_ID",
      "USER_ID",
      "REF_ID",
      "COMMENT_DATE",
      "COMMENT_IP",
      "COMMENT_CONTENT",
      "COMMENT_LOCATION",
      "COMMENT_TYPE",
      "PARENT_ID"
    )VALUES
    (
 -- t_cur_rec_id,
    IP_USER_ID,
    IP_REF_ID,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi'),
    IP_PUB_IP,
    T_PROCESS_CONTENT,
    IP_LOCATION,
    IP_COMMENT_TYPE,
    IP_PARENT_ID
    );
     OP_OPERATE_RESULT:=1;
     COMMIT; 
     --提醒用户
     
     BEGIN
     
      
      IF ip_comment_type = 1 THEN
        begin
        select user_id into t_notified_user from ca_play where record_id = IP_REF_ID;--查询被提醒用户ID
        
        select nickname into t_user_name  from common_user where login_id = IP_USER_ID;--查询当前用户昵称
          if CA_CHECK_NULL(t_user_name) =1 then
            CA_MESSAGES_tapi.INS(
              3,
              t_notified_user,
              '用户['||IP_USER_ID||'] 评论了您的趣事',
              IP_REF_ID
            );
          else 
            CA_MESSAGES_tapi.INS(
              3,
              t_notified_user,
               '用户['||t_user_name||'] 评论了您的趣事',
               IP_REF_ID
            );
          end if;
          
          
        end;
    
      elsif   ip_comment_type = 2 then 
        begin
            select USER_ID into t_notified_user from ca_play_comment where record_id = IP_PARENT_ID;--查询被提醒用户
            select nickname into t_user_name  from common_user where login_id = IP_USER_ID;--查询当前用户的昵称
        
              if CA_CHECK_NULL(t_user_name) =1 then
            CA_MESSAGES_tapi.INS(
              4,
              t_notified_user,
              '用户['||IP_USER_ID||'] 回复了您的趣事',
              IP_REF_ID
            );
          else 
            CA_MESSAGES_tapi.INS(
              4,
              t_notified_user,
               '用户['||t_user_name||'] 回复了您的趣事',
               IP_REF_ID
            );
        end if;
      end;
      END IF;
    
    END;
   
    
    
     EXCEPTION 
  WHEN OTHERS THEN
    t_message:='code:'||sqlcode||'sqlerrm;'||sqlerrm;
   OP_OPERATE_RESULT:=-1;
   ROLLBACK; 
END CA_PROC_PLAY_COMMENT_V2;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_PLAY_MARK
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_PLAY_MARK" 
(
  IP_MARK_TYPE IN NUMBER,
  IP_OBJECT_ID IN NUMBER,
  OP_OPERATE_CODE OUT NUMBER
)
AS
  
BEGIN
    OP_OPERATE_CODE:=0;
  --更新play数据库
        BEGIN
           IF IP_MARK_TYPE = 1 THEN --如果是顶
           UPDATE CA_PLAY
           SET  GOOD_COUNT = GOOD_COUNT+1
           WHERE RECORD_ID = IP_OBJECT_ID;
  
           
          ELSIF IP_MARK_TYPE = 2 THEN--鄙视
           UPDATE CA_PLAY
           SET  WORSE_COUNT = WORSE_COUNT+1
           WHERE RECORD_ID = IP_OBJECT_ID;
        
        END IF;
        END;
        OP_OPERATE_CODE:=1;
        COMMIT;  
EXCEPTION 
  WHEN OTHERS THEN 
     OP_OPERATE_CODE:=-1;
    ROLLBACK;
END CA_PROC_PLAY_MARK;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_RESET_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_RESET_USER" 
(
  IP_LOGIN_ID IN NVARCHAR2,
  --用户操作日志
  IP_CUR_LOGIN_ID IN NVARCHAR2,--当前登录用户IP
  IP_CUR_LOGIN_IP IN VARCHAR2, --当前登录用户IP
  OP_OPERATE_RESULT OUT NUMBER,
  OP_OPERATE_MSG OUT NVARCHAR2
)
AS
   T_REMARK NVARCHAR2(200);
   T_FUN NUMBER(1,0);
BEGIN
    --重置用户
    OP_OPERATE_RESULT:=-1;
    OP_OPERATE_MSG:='';
    UPDATE COMMON_USER
    SET STATUS        = -1,
       REGSTATUS       = -1,
       ENCRYPTPASSWORD = '',
       NICKNAME=''
        WHERE LOGIN_ID =IP_LOGIN_ID;
    --记录用户操作日志    
     --记录当前日志
  T_REMARK:=
  (
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
  )
  ||'用户['||IP_CUR_LOGIN_ID||']重置用户['||IP_LOGIN_ID||']成功';
  T_FUN:=CA_FUN_RECORD_OP_LOG
  (
    IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 60,1,T_REMARK
  )
  ;
   OP_OPERATE_RESULT:=1;
   OP_OPERATE_MSG:='重置用户成功';
   COMMIT; 
     
  EXCEPTION 
  WHEN OTHERS THEN
   OP_OPERATE_RESULT:=-1;
   OP_OPERATE_MSG:='发生异常sqlcode['||sqlcode||'],msg['||sqlerrm||']';
     T_REMARK:=
    (
      TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
    )
    ||'用户['||IP_CUR_LOGIN_ID||']重置用户['||IP_LOGIN_ID||']失败,错误如下:'||OP_OPERATE_MSG;
    T_FUN:=CA_FUN_RECORD_OP_LOG
    (
      IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 60, 0,T_REMARK
    )
    ;
   ROLLBACK; 

    
END CA_PROC_RESET_USER;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_SELECT_LIMIT_ALL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_SELECT_LIMIT_ALL" (
   OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM CA_PRI_GROUP_MAP order BY group_id asc ;

END CA_PROC_SELECT_LIMIT_ALL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_SELECT_LIMIT_BY_ID
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_SELECT_LIMIT_BY_ID" (
   IP_GID IN NUMBER,--传递0返回全部数据
   OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  IF IP_GID=0 THEN
     OPEN OP_SELECT_RESULT FOR
    SELECT * FROM CA_OP_CODE 
    ;
  ELSE 
  
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM CA_OP_CODE 
    WHERE GROUP_ID =IP_GID 
    ;
  END IF;
END CA_PROC_SELECT_LIMIT_BY_ID;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_SELECT_ROLE_ALL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_SELECT_ROLE_ALL" (
   OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM CA_ROLE ;

END CA_PROC_SELECT_ROLE_ALL;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_SYSCONFIG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_SYSCONFIG" 
(
  IP_KEY IN VARCHAR2,
  OP_OPERATE_CODE OUT NUMBER,
  OP_VALUE OUT VARCHAR2
  
)
AS
  
    
BEGIN

  SELECT 
    CONFIG_VALUE INTO OP_VALUE 
  FROM CA_SYS_CONFIG 
  WHERE CONFIG_KEY = IP_KEY;
  OP_OPERATE_CODE:=1;
  
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=0;
  WHEN OTHERS THEN
    OP_OPERATE_CODE:=-1;
END CA_PROC_SYSCONFIG;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_UPDATE_PASSWORD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_UPDATE_PASSWORD" (
  IP_USERNAME IN NVARCHAR2,
  IP_NEW_ENCYPT_PWD IN VARCHAR2,
  OP_UPDATE_RESULT OUT NUMBER
)
AS
BEGIN
  OP_UPDATE_RESULT:=-1;
  UPDATE "SYSTEM"."COMMON_USER" 
  SET "ENCRYPTPASSWORD"=IP_NEW_ENCYPT_PWD
  WHERE "LOGIN_ID"=IP_USERNAME;
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
  EXCEPTION
  WHEN OTHERS THEN
    OP_UPDATE_RESULT:=-1;
    ROLLBACK;
  
END CA_PROC_UPDATE_PASSWORD;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_UPDATE_SYSCONFIG
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_UPDATE_SYSCONFIG" 
(
  IP_KEY IN VARCHAR2,
  IP_VALUE IN VARCHAR2,
  OP_OPERATE_CODE OUT NUMBER
  
  
)
AS
  
    
BEGIN

  UPDATE  CA_SYS_CONFIG 
  SET CONFIG_VALUE = IP_VALUE
  WHERE CONFIG_KEY = IP_KEY;
  COMMIT;
  OP_OPERATE_CODE:=1;
  
  EXCEPTION
  WHEN OTHERS THEN
    OP_OPERATE_CODE:=-1; 
    ROLLBACK;
END CA_PROC_UPDATE_SYSCONFIG;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_WEB_SELECT_PRICODE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_WEB_SELECT_PRICODE" (
    IP_PRI_STR IN NVARCHAR2,
    OP_SELECT_CODE OUT NUMBER
   )
AS
BEGIN
        OP_SELECT_CODE :=0;
        SELECT  CODE_ID INTO OP_SELECT_CODE
        FROM CA_OP_CODE
        WHERE 
          CA_OP_CODE.OP_NAME=IP_PRI_STR;
          
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         OP_SELECT_CODE:=0;
      WHEN OTHERS THEN
         OP_SELECT_CODE:=-1;
  
END CA_PROC_WEB_SELECT_PRICODE;

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_WEB_WEBLOGIN
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_WEB_WEBLOGIN" (
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR ,
    OP_SELECT_CODE OUT NUMBER
   )
AS
BEGIN
  OP_SELECT_CODE :=0;
  OPEN OP_SELECT_RESULT FOR
      SELECT  
        WEB_ADMINCP_MEMBER.*,
        CA_ROLE.ROLE_NAME
      FROM WEB_ADMINCP_MEMBER,
        
        CA_ROLE
      WHERE
          WEB_ADMINCP_MEMBER.GROUP_ID   = CA_ROLE.ROLE_ID
         AND 
          WEB_ADMINCP_MEMBER.ULOGIN_ID=IP_USER_ID;
      
      
          
      OP_SELECT_CODE:=1;
      
    
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         OP_SELECT_CODE:=0;
      WHEN OTHERS THEN
         OP_SELECT_CODE:=-1;
  
END CA_PROC_WEB_WEBLOGIN;
  

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_WEB_WEBUSER_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_WEB_WEBUSER_DETAIL" (
    IP_USER_ID IN NVARCHAR2,
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_SELECT_CODE OUT NUMBER
   )
AS
BEGIN
  OP_SELECT_CODE :=0;
  OPEN OP_SELECT_RESULT FOR
       SELECT  *
        FROM WEB_ADMINCP_MEMBER
        WHERE 
          WEB_ADMINCP_MEMBER.ULOGIN_ID=IP_USER_ID;
          
      OP_SELECT_CODE:=1;
      
    
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         OP_SELECT_CODE:=0;
      WHEN OTHERS THEN
         OP_SELECT_CODE:=-1;
  
END CA_PROC_WEB_WEBUSER_DETAIL;
  
   

/
--------------------------------------------------------
--  DDL for Procedure CA_PROC_WEB_WEBUSER_UPDATE_PRI
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_PROC_WEB_WEBUSER_UPDATE_PRI" (
    IP_USER_ID IN NVARCHAR2,
    IP_PRI_STR IN VARCHAR2,
    --日志记录
    IP_CUR_LOGIN_ID IN NVARCHAR2,--当前登录用户IP
    IP_CUR_LOGIN_IP IN VARCHAR2, --当前登录用户IP
   
    OP_OPERATE_RESULT OUT NUMBER,
    OP_OPERATE_MSG OUT NVARCHAR2 
    
   )
AS
 T_REMARK NVARCHAR2(200);
   T_FUN NUMBER(1,0);
BEGIN

  UPDATE WEB_ADMINCP_MEMBER 
  SET
    PRI_LIST = IP_PRI_STR
  WHERE ULOGIN_ID = IP_USER_ID;
      
    
   --记录用户操作日志    
     --记录当前日志
  T_REMARK:=
  (
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
  )
  ||'用户['||IP_CUR_LOGIN_ID||']更改用户['||IP_USER_ID||']的权限成功';
  T_FUN:=CA_FUN_RECORD_OP_LOG
  (
    IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 63,1,T_REMARK
  )
  ;
   OP_OPERATE_RESULT:=1;
   OP_OPERATE_MSG:='更改用户权限失败';
   COMMIT; 
     
  EXCEPTION 
  WHEN OTHERS THEN
   OP_OPERATE_RESULT:=-1;
   OP_OPERATE_MSG:='发生异常sqlcode['||sqlcode||'],msg['||sqlerrm||']';
     T_REMARK:=
    (
      TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
    )
    ||'用户['||IP_CUR_LOGIN_ID||']更改用户权限['||IP_USER_ID||']失败,错误如下:'||OP_OPERATE_MSG;
    T_FUN:=CA_FUN_RECORD_OP_LOG
    (
      IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 63, 0,T_REMARK
    )
    ;
   ROLLBACK; 
  
END CA_PROC_WEB_WEBUSER_UPDATE_PRI;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_INSERT_MOBILE_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_INSERT_MOBILE_USER" 
  (
    IP_LOGIN_ID        IN NVARCHAR2,--登录ID
    IP_IDNUMBER        IN VARCHAR2, --身份证号码
    IP_STATUS          IN NUMBER,   --锁定状态
    IP_ENCRYPTPASSWORD IN VARCHAR2, --加密密码
    IP_NICKNAME        IN NVARCHAR2,--昵称
    IP_USER_TYPE       IN NUMBER,   --用户类型
    IP_TRUENAME        IN VARCHAR2, --用户真实名称
    ---以下为记录用户操作日志记录所需要的字段
    IP_CUR_LOGIN_ID IN NVARCHAR2,--当前登录用户IP
    IP_CUR_LOGIN_IP IN VARCHAR2, --当前登录用户IP
    --返回结果
    OP_INSERT_RESULT OUT NUMBER, --操作结果
    OP_MSG OUT NVARCHAR2 )
AS
  T_REMARK NVARCHAR2(200);
  T_FUN NUMBER(1,0);
BEGIN
  OP_INSERT_RESULT:=-1;
  --用户表
  INSERT
  INTO COMMON_USER
    (
      LOGIN_ID,
      IDNUMBER,
      STATUS,
      ENCRYPTPASSWORD,
      NICKNAME,
      REGSTATUS,
      USER_TYPE
    )
    VALUES
    (
      IP_LOGIN_ID,
      IP_IDNUMBER,
      IP_STATUS， IP_ENCRYPTPASSWORD,
      IP_NICKNAME,
      0,
      IP_USER_TYPE
    );
  --用户详细资料表
  INSERT
  INTO COMMON_USER_PROFILE
    (
      LOGIN_ID,
      TRUENAME
    )
    VALUES
    (
      IP_LOGIN_ID,
      IP_TRUENAME
    );
  --记录当前日志
  T_REMARK:=
  (
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
  )
  ||'用户['||IP_CUR_LOGIN_ID||']向USER表中插入用户['||IP_LOGIN_ID||']';
  T_FUN:=CA_FUN_RECORD_OP_LOG
  (
    IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 1, 1,T_REMARK
  )
  ;
  --记录用户注册日志
  PROC_RECORD_USER_STATUS
  (
    IP_LOGIN_ID, IP_CUR_LOGIN_IP, 'NO LOGIN', 'NO LOGIN', 1
  )
  ;
  OP_INSERT_RESULT:=1;
  OP_MSG          :='操作成功';
  COMMIT;
  --异常捕捉
EXCEPTION
WHEN OTHERS THEN
  OP_INSERT_RESULT:=-1;
   
  OP_MSG          :='发生异常sqlcode['||sqlcode||'],msg['||sqlerrm||']';
    T_REMARK:=
    (
      TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
    )
    ||'用户['||IP_CUR_LOGIN_ID||']重置用户['||IP_LOGIN_ID||']失败,错误如下:'||OP_MSG;
    T_FUN:=CA_FUN_RECORD_OP_LOG
    (
      IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 1, 0,T_REMARK
    )
    ;
  ROLLBACK;
END CA_WEB_INSERT_MOBILE_USER;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_PROC_CHK_TRANSSTATUS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_PROC_CHK_TRANSSTATUS" 
(
  IP_DATAFILE_PATH IN NVARCHAR2,
  OP_CHECK_STATUS OUT NUMBER
)
AS
T_SELECT_COUNT NUMBER(1,0);
BEGIN
  T_SELECT_COUNT:=-1;
  SELECT COUNT(*) INTO T_SELECT_COUNT  FROM CA_DATATRANS WHERE DATA_SOURCE=IP_DATAFILE_PATH;
  
  IF T_SELECT_COUNT=0 THEN
    OP_CHECK_STATUS :=0;
  ELSIF   T_SELECT_COUNT>0 THEN
    OP_CHECK_STATUS :=1;
  END IF;
  
EXCEPTION
 WHEN NO_DATA_FOUND THEN
    OP_CHECK_STATUS :=0;
END CA_WEB_PROC_CHK_TRANSSTATUS;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_PROC_LOG_LIST
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_PROC_LOG_LIST" 
(
  IP_PAGE_INDEX IN NUMBER,--页索引
  IP_PAGE_SIZE IN NUMBER,--页大小
  IP_DEVICE_TYPE IN NUMBER,--设备类型:1表示Android,2表示IOS,0表示所有
  IP_PROC_STATUS IN NUMBER,--处理状态:2表示所有,0表示未处理,1表示已处理
  OP_SELECT_RESULT OUT SYS_REFCURSOR,--查询结果集
  OP_COUNT_ALL OUT NUMBER--查询总数
)
AS
  V_CUR_PAGE NUMBER;
BEGIN
 V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
  --根据设备类型进行检索
  CASE IP_DEVICE_TYPE
  WHEN 0 THEN
    --所有设备
    --根据处理状态进行检索
    CASE IP_PROC_STATUS
    WHEN 0 THEN
      --未处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=0 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=0 ;
    
    WHEN 1 THEN
    --已处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
       SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 ;
    WHEN 2 THEN 
    
    --所有状态
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG  ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
       SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG  ;
    END CASE ;
  WHEN 1 THEN
  
    --Android设备
    --根据处理状态进行检索
    CASE IP_PROC_STATUS
    WHEN 0 THEN
       --未处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=0 AND DEVICE_TYPE=1 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
        SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE  PROCESS_STATUS=0 AND DEVICE_TYPE=1  ;
    WHEN 1 THEN
         --已处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 AND DEVICE_TYPE=1 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
       SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 AND DEVICE_TYPE=1 ;
    WHEN 2 THEN 
    --全部状态
     OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE  DEVICE_TYPE=1 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
         SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE DEVICE_TYPE=1 ;
    END CASE ;
    
  WHEN 2 THEN
   --IOS设备
    --根据处理状态进行检索
    CASE IP_PROC_STATUS
    WHEN 0 THEN
       --未处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=0 AND DEVICE_TYPE=2 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
         SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=0 AND DEVICE_TYPE=2;
    WHEN 1 THEN
      --已处理
       OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 AND DEVICE_TYPE=2 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
          SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE PROCESS_STATUS=1 AND DEVICE_TYPE=2;
    WHEN 2 THEN 
     --全部状态
     OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_MOBILE_LOG WHERE  DEVICE_TYPE=2 ORDER BY UPLOAD_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
          SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_MOBILE_LOG WHERE  DEVICE_TYPE=2;
    END CASE ;
    
  END CASE;
    
 
  
END CA_WEB_PROC_LOG_LIST;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_PROC_TRANSLOG_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_PROC_TRANSLOG_PAGE" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR,
  OP_COUNT_ALL OUT NUMBER
)AS
 V_CUR_PAGE NUMBER;
BEGIN
V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
    OPEN OP_SELECT_RESULT FOR
       SELECT * FROM ( 
       SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM CA_DATATRANS  ORDER BY OPERATE_DATE DESC ) T)
       )
       WHERE
       RN >= (V_CUR_PAGE + 1) AND 
       RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
    
      SELECT COUNT(*) INTO OP_COUNT_ALL  FROM CA_DATATRANS ;
  
END CA_WEB_PROC_TRANSLOG_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_RECORD_DATATRANS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_RECORD_DATATRANS" 
(
 IP_DATA_SOURCE IN NVARCHAR2,--数据源
 IP_DATA_COUNT IN NUMBER,--插入数量
 IP_OPERATE_IP IN VARCHAR2,--操作IP
 IP_LOGIN_ID IN NVARCHAR2,--操作人
 IP_OPERATE_REMARK IN NVARCHAR2,--操作备注
 OP_INSERT_RESULT OUT NUMBER--操作结果
)
AS

BEGIN
  OP_INSERT_RESULT:=-1;
  
  INSERT INTO CA_DATATRANS 
  (
    DATA_SOURCE,
    DATA_COUNT,
    OPERATE_DATE,
    OPERATE_IP,
    OPERATE_MEMBER,
    OPERATE_REMARK
  )
  VALUES(
    IP_DATA_SOURCE,
    IP_DATA_COUNT,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss'),
    IP_OPERATE_IP,
    IP_LOGIN_ID,
    IP_OPERATE_REMARK
  );
   OP_INSERT_RESULT:=1;
  COMMIT;
 
  
  EXCEPTION
  WHEN OTHERS THEN
      OP_INSERT_RESULT:=-1;
      ROLLBACK;
END CA_WEB_RECORD_DATATRANS; 

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TOOGLE_MARK
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TOOGLE_MARK" (
  IP_LOG_ID IN NUMBER,
  IP_PROC_STATUS IN NUMBER,
  OP_UPDATE_RESULT OUT NUMBER
)
AS
  T_PROC_STATUS NUMBER(1,0);
BEGIN

  IF IP_PROC_STATUS=0 THEN
     T_PROC_STATUS:=1;
  ELSIF  IP_PROC_STATUS=1 THEN
      T_PROC_STATUS:=0;
  END IF;
  OP_UPDATE_RESULT:=-1;
  UPDATE CA_MOBILE_LOG
  SET 
    PROCESS_STATUS=T_PROC_STATUS,
    PROC_FINISH_DATE= TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
  WHERE LOG_ID=IP_LOG_ID;
  
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  OP_UPDATE_RESULT:=-1;
END CA_WEB_TOOGLE_MARK;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_GRADE_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_GRADE_FROM_PSQL" (
    IP_GRADENO    IN VARCHAR2,
    IP_STUNO      IN VARCHAR2,
    IP_LESSONNO   IN VARCHAR2,
    IP_GRADE      IN NVARCHAR2,
    IP_EXPERIENCE IN VARCHAR2,
    IP_SEMESTER   IN NVARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_GRADE
    (
      GRADENO,
      STUNO,
      LESSONNO,
      GRADE,
      EXPERIENCE,
      "SEMESTER"
    )
    VALUES
    (
      IP_GRADENO,
      IP_STUNO,
      IP_LESSONNO,
      IP_GRADE,
      IP_EXPERIENCE,
      IP_SEMESTER
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='GRADENO['||IP_GRADENO||'],STUNO['||IP_STUNO||'],LESSONNO['||IP_LESSONNO||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='GRADENO['||IP_GRADENO||'],STUNO['||IP_STUNO||'],LESSONNO['||IP_LESSONNO||']';
  ROLLBACK;
END CA_WEB_TRANS_GRADE_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_LESSON_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_LESSON_FROM_PSQL" (
    IP_CLASSNO IN VARCHAR2,
    IP_CLASSNAME IN NVARCHAR2,
    IP_CREDIT IN VARCHAR2,
    IP_PERIOD IN VARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_LESSON
    (
      CLASSNO,
      CLASSNAME,
      CREDIT,
      PERIOD
      
    )
    VALUES
    (
      IP_CLASSNO,
      IP_CLASSNAME,
      IP_CREDIT,
      IP_PERIOD
     
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='CLASSNO['||IP_CLASSNO||'],CLASSNAME['||IP_CLASSNAME||'],LESSONNO['||IP_CREDIT||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='CLASSNO['||IP_CLASSNO||'],CLASSNAME['||IP_CLASSNAME||'],LESSONNO['||IP_CREDIT||']';
  ROLLBACK;
END CA_WEB_TRANS_LESSON_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_PKB_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_PKB_FROM_PSQL" (
    IP_GRADENO IN NVARCHAR2,
    IP_WEEKOFSEMESTER IN VARCHAR2,
    IP_DAYOFWEEK IN VARCHAR2,
    IP_NOOFDAY IN VARCHAR2,
    IP_LESSON_ADD IN NVARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_PKB
    (
      GRADENO,
      WEEKOFSEMESTER,
      DAYOFWEEK,
      NOOFDAY, 
      LESSON_ADD
    )
    VALUES
    (
      IP_GRADENO,
      IP_WEEKOFSEMESTER,
      IP_DAYOFWEEK,
      IP_NOOFDAY,
      IP_LESSON_ADD
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
 ROLLBACK;
END CA_WEB_TRANS_PKB_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_SKB_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_SKB_FROM_PSQL" (
    IP_STUNO IN VARCHAR2,
    IP_LESSONNO IN NVARCHAR2,
    IP_GRADENO IN VARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_SKB
    (
      STUNO,
      LESSONNO,
      GRADENO
     
      
    )
    VALUES
    (
      IP_STUNO,
      IP_LESSONNO,
      IP_GRADENO
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='IP_STUNO['||IP_STUNO||'],IP_LESSONNO['||IP_LESSONNO||'],IP_GRADENO['||IP_GRADENO||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='IP_STUNO['||IP_STUNO||'],IP_LESSONNO['||IP_LESSONNO||'],IP_GRADENO['||IP_GRADENO||']';
  ROLLBACK;
END CA_WEB_TRANS_SKB_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_TEACHER_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_TEACHER_FROM_PSQL" (
    IP_TEACHERNO IN NVARCHAR2,
    IP_TEACHERNAME IN NVARCHAR2,
    IP_GENDER IN NVARCHAR2,
    IP_MAJOR IN VARCHAR2,
    IP_LESSON IN NVARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_TEACHER
    (
      TEACHERNO,
      TEACHERNAME,
      GENDER,
      MAJOR, 
      LESSON
    )
    VALUES
    (
      IP_TEACHERNO,
      IP_TEACHERNAME,
      IP_GENDER,
      IP_MAJOR,
      IP_LESSON
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
 ROLLBACK;
END CA_WEB_TRANS_TEACHER_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_T_SKB_FROM_PSQL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_T_SKB_FROM_PSQL" (
    IP_MAJOR_DIRECTION IN NVARCHAR2,
    IP_CLASSNO IN NVARCHAR2,
    IP_LESSON_TYPE IN NVARCHAR2,
    IP_TEACHER_NO IN VARCHAR2,
    IP_LESSON_NO IN NVARCHAR2,
    OP_INSERT_RESULT OUT NUMBER,  --操作结果,0表示失败,1表示成功
    OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
  )
AS
BEGIN
  OP_INSERT_RESULT:=0;
  --USER
  INSERT
  INTO MOBILE_T_SKB
    (
      MAJOR_DIRECTION,
      CLASSNO,
      LESSON_TYPE,
      TEACHER_NO, 
      LESSON_NO
    )
    VALUES
    (
      IP_MAJOR_DIRECTION,
      IP_CLASSNO,
      IP_LESSON_TYPE,
      IP_TEACHER_NO,
      IP_LESSON_NO
    );
  OP_INSERT_RESULT:=1;
  COMMIT;
EXCEPTION
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
  ROLLBACK;
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:='SQLCODE['||SQLCODE||'],ERRMSG['||SQLERRM||']';
 ROLLBACK;
END CA_WEB_TRANS_T_SKB_FROM_PSQL;

/
--------------------------------------------------------
--  DDL for Procedure CA_WEB_TRANS_USER_FROM_NET
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."CA_WEB_TRANS_USER_FROM_NET" 
(
  IP_LOGIN_ID IN NVARCHAR2,--用户登录ID
  IP_IDNUMBER IN VARCHAR2,--身份证号码
  IP_USER_TYPE IN NUMBER,---用户类型
  IP_TRUENAME IN NVARCHAR2,--用户真实名称
  OP_INSERT_RESULT OUT NUMBER,--操作结果,0表示失败,1表示成功
  OP_FAILED_USERID OUT NVARCHAR2--只有失败时候,返回用户学号
)
AS 
BEGIN
  OP_INSERT_RESULT:=0;
  --USER 
  INSERT INTO COMMON_USER 
  (LOGIN_ID,
  IDNUMBER,
  USER_TYPE
  )VALUES(
  IP_LOGIN_ID,
  IP_IDNUMBER,
  IP_USER_TYPE
  );
  
  --USER PROFILE
  
  INSERT INTO COMMON_USER_PROFILE
  (
  LOGIN_ID,
  TRUENAME
  )
  VALUES(
  IP_LOGIN_ID,
  IP_TRUENAME);
  
  OP_INSERT_RESULT:=1;
  COMMIT;
  
  
EXCEPTION 
WHEN Dup_val_on_index THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:=IP_LOGIN_ID;
  ROLLBACK; 
WHEN OTHERS THEN
  OP_INSERT_RESULT:=0;
  OP_FAILED_USERID:=IP_LOGIN_ID;
  ROLLBACK; 
  

  
END CA_WEB_TRANS_USER_FROM_NET;

/
--------------------------------------------------------
--  DDL for Procedure ORA$_SYS_REP_AUTH
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."ORA$_SYS_REP_AUTH" as
begin
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_calldest TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_error TO SYS ' ||
                 'WITH GRANT OPTION';
  EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_destination TO SYS ' ||
                 'WITH GRANT OPTION';
end;

/
--------------------------------------------------------
--  DDL for Procedure PRC_ENTERTAINMENT_MARK
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRC_ENTERTAINMENT_MARK" (mark_type in number,rID in number,update_result out sys_refcursor )
as

begin

--更新评分
  case mark_type
    when 1 then 
    update COMMON_ENTERTAINMENT set GOOD_MARKS=GOOD_MARKS+1 where record_id=rID;
     dbms_output.put_line('rID :'|| 1);  
    when 2 then 
        dbms_output.put_line('rID :'|| 2);  
      update COMMON_ENTERTAINMENT set NORMAL_MARKS=NORMAL_MARKS+1 where record_id=rID;
    when 3 then 
      dbms_output.put_line('rID :'|| 3);  
      update COMMON_ENTERTAINMENT set WROSE_MARKS=WROSE_MARKS+1 where record_id=rID;
  end case;
   
  open update_result for select * from COMMON_ENTERTAINMENT where record_id=rID;
  
end prc_entertainment_mark;

/
--------------------------------------------------------
--  DDL for Procedure PROC_ADVERTISMENT_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_ADVERTISMENT_PAGE" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
  SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
  SELECT MOBILE_ADVERTISMENT.*,MOBILE_ADVIRTISMENT_TYPE."TYPE_NAME" 
  FROM MOBILE_ADVERTISMENT,MOBILE_ADVIRTISMENT_TYPE
  WHERE MOBILE_ADVERTISMENT."TYPE"=MOBILE_ADVIRTISMENT_TYPE.TYPE_ID
  ORDER BY PUB_DATE DESC ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
  
END PROC_ADVERTISMENT_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_BAT_DELETE_NOTIFICATION
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_BAT_DELETE_NOTIFICATION" 
(
IP_IN_STR IN VARCHAR2,
OP_DELETE_RESULT OUT NUMBER
)
AS
V_STR NVARCHAR2(100);
BEGIN
  OP_DELETE_RESULT:=-1;
  --  V_STR := '('||||')';
  -- DBMS_OUTPUT.PUT_LINE('IP_IN_STR='||V_STR);
 -- DELETE FROM MOBILE_SCHOOL_NOTIFICATION WHERE NOTIFICATION_ID IN V_STR ;
   DELETE  FROM MOBILE_SCHOOL_NOTIFICATION WHERE NOTIFICATION_ID IN  IP_IN_STR;
  COMMIT;
  
  OP_DELETE_RESULT:=1;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM||'OP_DELETE_RESULT='||OP_DELETE_RESULT);
      ROLLBACK;
      OP_DELETE_RESULT:=-1;
      
END PROC_BAT_DELETE_NOTIFICATION;

/
--------------------------------------------------------
--  DDL for Procedure PROC_CHECK_USERINFO
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_CHECK_USERINFO" (
    IP_USER_ID  IN NVARCHAR2,
    IP_CARD_SUB IN VARCHAR2,
    OP_OPERATE_CODE OUT NUMBER )
AS
  T_STR_CARD     VARCHAR2(20);
  T_CARD_LEN     NUMBER(2,0);
  T_STR_SUB_CARD VARCHAR2(4);
BEGIN
  --COMMIT;
  -- FIND USER'S ID CARD
  OP_OPERATE_CODE:=-1;
  SELECT IDNUMBER INTO T_STR_CARD FROM COMMON_USER WHERE LOGIN_ID = IP_USER_ID;
  --CAL THE LENGTH
  T_CARD_LEN:=LENGTH(T_STR_CARD);
  --THE LAST 4 CHARS
  T_STR_SUB_CARD   := SUBSTR(T_STR_CARD,(T_CARD_LEN-3),4);
  
  dbms_output.put_line('SUB CAR:'||T_STR_SUB_CARD);
  
  IF IP_CARD_SUB    =T_STR_SUB_CARD THEN
     dbms_output.put_line('T_STR_SUB_CARD=IP_CARD_SUB');
    OP_OPERATE_CODE:=1;
  ELSE
    dbms_output.put_line('T_STR_SUB_CARD!=IP_CARD_SUB');
    OP_OPERATE_CODE:=3;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    OP_OPERATE_CODE:=2;
     dbms_output.put_line('NO SUCH USER ');
    WHEN OTHERS THEN 
     dbms_output.put_line('OTHER EXCEPTION OCCURED ');
    OP_OPERATE_CODE:=4;
END PROC_CHECK_USERINFO;

/
--------------------------------------------------------
--  DDL for Procedure PROC_CHK_UPDATE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_CHK_UPDATE" (
REQUEST_TYPE IN NUMBER,
CHK_RESULT OUT SYS_REFCURSOR
) 
AS
MAX_VERSION_CODE NUMBER;
BEGIN

  SELECT MAX(APPVERSIONCODE) INTO MAX_VERSION_CODE  FROM COMMON_APP_PUB WHERE APPTYPE=REQUEST_TYPE;
 
  OPEN CHK_RESULT FOR
    SELECT 
      APPVERSIONNAME,APPVERSIONCODE,APP_PATH,UPDATECONTENT
    FROM COMMON_APP_PUB
    WHERE APPVERSIONCODE = MAX_VERSION_CODE AND APPTYPE = REQUEST_TYPE;
  

END PROC_CHK_UPDATE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_CHK_USER_UNIQE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_CHK_USER_UNIQE" (USERNAME IN VARCHAR2,CHK_RESULT OUT NUMBER)
AS

BEGIN
  SELECT COUNT(*) INTO CHK_RESULT FROM COMMON_USER WHERE LOGIN_ID=USERNAME;
  
END PROC_CHK_USER_UNIQE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_CREATE_WEB_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_CREATE_WEB_USER" (
U_NAME IN VARCHAR2,--用户名
U_PASSWORD IN VARCHAR2,--加密密码
U_NICKNAME IN VARCHAR2,--昵称
U_HEAD IN VARCHAR2,--用户头像
U_GROUP_ID IN NUMBER,
--记录日志
IP_CUR_LOGIN_ID IN NVARCHAR2,--当前登录用户IP
IP_CUR_LOGIN_IP IN VARCHAR2, --当前登录用户IP
OP_OPERATE_RESULT OUT NUMBER,
OP_OPERATE_MSG OUT NVARCHAR2
)
AS
  T_REMARK NVARCHAR2(200);
  T_FUN NUMBER(1,0);
  
BEGIN
    OP_OPERATE_RESULT:=-1;
    OP_OPERATE_MSG:='';
    
   INSERT INTO WEB_ADMINCP_MEMBER
   (
    ULOGIN_ID,
    GROUP_ID,
    ENCYPTPASSWORD,
    NICKNAME,
    HEADPATH
   )
   VALUES(
   U_NAME,
   U_GROUP_ID,
  -- U_PRI_LIST,
   U_PASSWORD,
   U_NICKNAME,
   U_HEAD
   );
 
 
   T_REMARK:=
  (
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
  )
  ||'用户['||IP_CUR_LOGIN_ID||']创建后台用户['||U_NAME||']成功';
  T_FUN:=CA_FUN_RECORD_OP_LOG
  (
    IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 3,1,T_REMARK
  )
  ;
  
   OP_OPERATE_RESULT:=1;
   OP_OPERATE_MSG:='重置用户成功';
   COMMIT; 
   
   
   EXCEPTION 
  WHEN OTHERS THEN
   OP_OPERATE_RESULT:=-1;
   OP_OPERATE_MSG:='发生异常sqlcode['||sqlcode||'],msg['||sqlerrm||']';
     T_REMARK:=
    (
      TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi:ss')
    )
    ||'用户['||IP_CUR_LOGIN_ID||']创建后台用户['||U_NAME||']失败,错误如下:'||OP_OPERATE_MSG;
    T_FUN:=CA_FUN_RECORD_OP_LOG
    (
      IP_CUR_LOGIN_ID, IP_CUR_LOGIN_IP, 3, 0,T_REMARK
    )
    ;
   ROLLBACK; 
END PROC_CREATE_WEB_USER;

/
--------------------------------------------------------
--  DDL for Procedure PROC_DELETE_ACTIVITY
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_DELETE_ACTIVITY" (
  IP_ID IN NUMBER,
  OP_DELETE_RESULT OUT NUMBER
) 
AS 
BEGIN
  DELETE FROM COMMON_ACTIVITY WHERE RECORD_ID=IP_ID;
  COMMIT;
  OP_DELETE_RESULT:=1;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
    OP_DELETE_RESULT:=-1;
END PROC_DELETE_ACTIVITY;
 

/
--------------------------------------------------------
--  DDL for Procedure PROC_DELETE_MESSAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_DELETE_MESSAGE" (
    p_RECORD_ID IN COMMON_MESSAGE.RECORD_ID%type )
IS
BEGIN
  DELETE FROM COMMON_MESSAGE WHERE RECORD_ID = p_RECORD_ID;
END PROC_DELETE_MESSAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_DELETE_NOTIFICAION
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_DELETE_NOTIFICAION" (
IP_ID IN NUMBER
)
AS
BEGIN 
 DELETE FROM MOBILE_SCHOOL_NOTIFICATION WHERE NOTIFICATION_ID=IP_ID;
END PROC_DELETE_NOTIFICAION;

/
--------------------------------------------------------
--  DDL for Procedure PROC_GET_DEPTNAME
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_GET_DEPTNAME" 
(
  IP_COLLEGE_NO IN NVARCHAR2,
  OP_COLLEGE_NAME OUT NVARCHAR2
)
AS 
BEGIN
SELECT COLLEGE_NAME INTO OP_COLLEGE_NAME FROM COMMON_DEPT WHERE COLLEGE_NO=IP_COLLEGE_NO;
END PROC_GET_DEPTNAME;

/
--------------------------------------------------------
--  DDL for Procedure PROC_GET_MARJORNAME
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_GET_MARJORNAME" 
(
  IP_MARJOR_NO IN NVARCHAR2,
  IP_COLLEGE_NO IN NVARCHAR2,
  OP_MARJOR_NAME OUT NVARCHAR2
)
AS 
BEGIN
SELECT MAJOR_NAME INTO OP_MARJOR_NAME FROM COMMON_MAJOR WHERE MAJOR_NO=IP_MARJOR_NO AND COLLEGE_NO=IP_COLLEGE_NO;
END PROC_GET_MARJORNAME;

/
--------------------------------------------------------
--  DDL for Procedure PROC_GET_USER_PROFILE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_GET_USER_PROFILE" (
IP_USER_ID IN VARCHAR2,
OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT 
      COMMON_USER.NICKNAME,
      COMMON_USER_PROFILE.SIGNATURE,
      COMMON_USER_PROFILE.GENDER,
      COMMON_USER_PROFILE.BIRTHYEAR,
      COMMON_USER_PROFILE.BIRTHMONTH,
      COMMON_USER_PROFILE.BIRTHDAY,
      COMMON_USER_PROFILE.INTRODUCE,
      COMMON_USER_PROFILE.USERHEAD,
      COMMON_USER_PROFILE.ADDRESS,
      COMMON_USER_PROFILE.MAJORID,
      COMMON_USER_PROFILE.CONSTELLATION,
      COMMON_USER_PROFILE.AGE
    FROM COMMON_USER,COMMON_USER_PROFILE
    WHERE 
      COMMON_USER.LOGIN_ID=IP_USER_ID 
      AND 
      COMMON_USER_PROFILE.LOGIN_ID=COMMON_USER.LOGIN_ID;


END PROC_GET_USER_PROFILE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_INSERT_MESSAGE_REPORT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_INSERT_MESSAGE_REPORT" (
USER_ID IN VARCHAR2,
MESSAGE_ID IN NUMBER,
IP_DATE IN VARCHAR2,
INSERT_RESULT OUT NUMBER
)
AS 
BEGIN
  INSERT_RESULT:=-1;
  INSERT INTO COMMON_MESSAGE_STATUS(LOGIN_ID,MSG_ID,STATUS,READ_DATE) VALUES(USER_ID,MESSAGE_ID,1,IP_DATE);
  
  INSERT_RESULT:=1;
  
END PROC_INSERT_MESSAGE_REPORT;

/
--------------------------------------------------------
--  DDL for Procedure PROC_IOS_GET_COMMENTCOUNTS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_IOS_GET_COMMENTCOUNTS" (
IP_ENTERTAINMENT_ID IN NUMBER,
OP_COMMENT_COUNT OUT NUMBER
)
AS
BEGIN
  SELECT COUNT(*) INTO OP_COMMENT_COUNT FROM COMMON_ENTERTAINMENT_COMMENT WHERE REF_ID=IP_ENTERTAINMENT_ID;
  
END PROC_IOS_GET_COMMENTCOUNTS;

/
--------------------------------------------------------
--  DDL for Procedure PROC_IOS_GET_COMMENT_COUNTS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_IOS_GET_COMMENT_COUNTS" (
IP_ENTERTAINMENT_ID IN NUMBER,
OP_COMMENT_COUNT OUT NUMBER
)
AS
BEGIN
  SELECT COUNT(*) INTO OP_COMMENT_COUNT FROM COMMON_ENTERTAINMENT_COMMENT WHERE REF_ID=IP_ENTERTAINMENT_ID;
  
END PROC_IOS_GET_COMMENT_COUNTS;

/
--------------------------------------------------------
--  DDL for Procedure PROC_LOCK_ACCOUNT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_LOCK_ACCOUNT" (
  IP_USER_ID IN NVARCHAR2,
  IP_STATUS IN NUMBER,
  OP_UPDATE_RESULT OUT NUMBER
)
AS
BEGIN
  OP_UPDATE_RESULT:=-1;
  UPDATE COMMON_USER 
  SET 
  STATUS=IP_STATUS
  WHERE LOGIN_ID=IP_USER_ID;
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
  EXCEPTION 
  WHEN OTHERS THEN
    ROLLBACK;
    OP_UPDATE_RESULT:=-1;
    
END PROC_LOCK_ACCOUNT;

/
--------------------------------------------------------
--  DDL for Procedure PROC_LOGIN
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_LOGIN" (
IP_LOGIN_ID IN NVARCHAR2,
OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS

BEGIN
  OPEN OP_SELECT_RESULT FOR  
    SELECT * FROM  COMMON_USER
      WHERE LOGIN_ID=IP_LOGIN_ID;
  
END PROC_LOGIN;

/
--------------------------------------------------------
--  DDL for Procedure PROC_PAGE_NOTIFICATION
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_PAGE_NOTIFICATION" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  SELECT_RESULT OUT SYS_REFCURSOR
)
AS
V_CUR_PAGE NUMBER;
BEGIN

  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN SELECT_RESULT
  FOR
    SELECT 
      V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_ID,
      V_MOBILE_PAGE_NOTIFICATION.NOFIFICATION_TITLE,
      MOBILE_NOTIFICATION_TYPE.SCHOOL_NOTIFICATION_TYPE_NAME,
      V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_PUBLISH_DATE
    
    FROM V_MOBILE_PAGE_NOTIFICATION,MOBILE_NOTIFICATION_TYPE
    WHERE
        RN >= (V_CUR_PAGE + 1) AND 
        RN <= (V_CUR_PAGE + IP_PAGE_SIZE) AND
        MOBILE_NOTIFICATION_TYPE.NOTIFICATION_TYPE_ID=V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_TYPE_ID;

END PROC_PAGE_NOTIFICATION;

/
--------------------------------------------------------
--  DDL for Procedure PROC_PARTTIMEJOB_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_PARTTIMEJOB_PAGE" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
  SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM MOBILE_PART_TIME_JOB ORDER BY PARTIME_JOB_PUBLISH_DATE DESC ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
  
END PROC_PARTTIMEJOB_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_PUB_MESSAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_PUB_MESSAGE" (
 MSG_METHOD IN NUMBER,
 MSG_TYPE IN NUMBER,
 PUSH_TYPE IN NUMBER,
 MSG_TITLE IN NVARCHAR2,
 MSG_CONTENT IN NVARCHAR2,
 PUB_DATE IN NVARCHAR2,
 PUB_IP IN VARCHAR2,
 PUB_AUTHOR IN NVARCHAR2,
 RECORD_ID OUT NUMBER
 )   
AS 
  t_rec_id  COMMON_MESSAGE.RECORD_ID%type;
  

BEGIN 
  t_rec_id:=SEQ_MESSAGE_ID.NEXTVAL;

  INSERT INTO COMMON_MESSAGE
    VALUES(
      t_rec_id,
      MSG_METHOD,
      MSG_TYPE,
      PUSH_TYPE,
      MSG_TITLE,
      MSG_CONTENT,
      PUB_DATE,
      PUB_IP,
      PUB_AUTHOR
    );
    RECORD_ID:=t_rec_id;
    --消息
    
    DBMS_OUTPUT.PUT_LINE('执行成功,RECORD_ID='||RECORD_ID);
END PROC_PUB_MESSAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_PUB_PLAY
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_PUB_PLAY" (
IP_USER_ID IN NVARCHAR2,--用户id
IP_CONTENT IN CLOB,--正文
IP_IMG_THUMB_PATH IN NVARCHAR2,--缩略图
IP_IMG_ORIGIN_PATH IN NVARCHAR2,--原图
IP_FLAG_HIDE IN NUMBER,--是否隐藏。1表示隐藏,0表示显示
IP_LOCATION IN NVARCHAR2,--地址
IP_PUB_IP IN NVARCHAR2,--IP
OP_OPERATE_RESULT OUT NUMBER--操作结果
)
AS
T_RESULT NVARCHAR2(200);
BEGIN
    T_RESULT:=CA_FUN_CHK_SENSITIVE(IP_CONTENT);
    OP_OPERATE_RESULT:=-1;
    INSERT INTO CA_PLAY
    (
      "CONTENT",
      "IMG_THUMB_PATH",
      "IMG_ORIGIN_PATH",
      "FLAG_HIDE",
      "LOCATION",
      "PUB_IP",
      "PUB_DATE",
      "USER_ID"
      
    )VALUES
    (
    T_RESULT,
    IP_IMG_THUMB_PATH,
    IP_IMG_ORIGIN_PATH,
    IP_FLAG_HIDE,
    IP_LOCATION,
    IP_PUB_IP,
    TO_CHAR(SYSDATE(),'yyyy-mm-dd hh24:mi'),
    IP_USER_ID
    );
    
     OP_OPERATE_RESULT:=1;
     COMMIT; 
    
     EXCEPTION 
  WHEN OTHERS THEN
   OP_OPERATE_RESULT:=-1;
   ROLLBACK; 
END PROC_PUB_PLAY;

/
--------------------------------------------------------
--  DDL for Procedure PROC_PUSH_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_PUSH_PAGE" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
  SELECT * FROM (SELECT T.*,ROWNUM RN FROM (
  SELECT COMMON_MESSAGE.*, COMMON_MESSAGE_TYPE.MESSAGE_NAME FROM COMMON_MESSAGE,COMMON_MESSAGE_TYPE
  WHERE MSG_METHOD = 1 AND  COMMON_MESSAGE.MSG_TYPE = COMMON_MESSAGE_TYPE."ID"
  ORDER BY PUB_DATE DESC ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
  
END PROC_PUSH_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_ACTIVITY_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_ACTIVITY_DETAIL" (
  IP_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
) 
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM COMMON_ACTIVITY WHERE RECORD_ID=IP_ID;
END PROC_QUERY_ACTIVITY_DETAIL;

 

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_ACTIVITY_DETAILV2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_ACTIVITY_DETAILV2" (
  IP_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
) 
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    select 
      COMMON_ACTIVITY.RECORD_ID,
      COMMON_ACTIVITY.PUB_DATE,
      COMMON_ACTIVITY.PUB_TITLE,
      COMMON_ACTIVITY.PUB_CONTENT,
      WEB_ADMINCP_MEMBER.NICKNAME, 
      WEB_ADMINCP_MEMBER.HEADPATH USERHEAD
      from COMMON_ACTIVITY,WEB_ADMINCP_MEMBER
      WHERE 
      COMMON_ACTIVITY.U_LOGINID=WEB_ADMINCP_MEMBER.ULOGIN_ID
      and RECORD_ID = IP_ID;
END PROC_QUERY_ACTIVITY_DETAILV2;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_CURRENT_WEEK
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_CURRENT_WEEK" (
CURRENT_WEEK OUT NUMBER
)
AS
  v_date date default sysdate; 
BEGIN
  
--select WEEK_NO INTO CURRENT_WEEK from MOBILE_LESSON_ASSIGN WHERE to_date(BEGIN_DATE,'yyyy-mm-dd hh:mi:ss') <= to_date(SYSDATE,'yyyy-mm-dd hh:mi:ss') AND to_date(END_DATE,'yyyy-mm-dd hh:mi:ss') >= to_date(SYSDATE,'yyyy-mm-dd hh:mi:ss');
  select WEEK_NO INTO CURRENT_WEEK from MOBILE_LESSON_ASSIGN WHERE to_date(BEGIN_DATE,'yyyy-mm-dd hh24:mi:ss') <= v_date AND to_date(END_DATE,'yyyy-mm-dd hh24:mi:ss') >=v_date;
  
-- DBMS_OUTPUT.PUT_LINE('CURRENT WEEK:'||0);

EXCEPTION
 WHEN OTHERS THEN
   CURRENT_WEEK:=888;
  
END PROC_QUERY_CURRENT_WEEK;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_PAGE_NOTIFICATION
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_PAGE_NOTIFICATION" (
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE  IN NUMBER,
    IP_NOTIFICATION_TYPE IN NUMBER,
    IP_KEYWORDS IN NVARCHAR2,
    SELECT_RESULT OUT SYS_REFCURSOR )
AS
  V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  
  IF IP_NOTIFICATION_TYPE=-1 THEN
    OPEN SELECT_RESULT FOR 
    SELECT 
    V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_ID,
    V_MOBILE_PAGE_NOTIFICATION.NOFIFICATION_TITLE,
    MOBILE_NOTIFICATION_TYPE.SCHOOL_NOTIFICATION_TYPE_NAME,
    V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_PUBLISH_DATE 
    FROM V_MOBILE_PAGE_NOTIFICATION,
         MOBILE_NOTIFICATION_TYPE 
    WHERE RN >= (V_CUR_PAGE + 1) AND
          RN <= (V_CUR_PAGE + IP_PAGE_SIZE) AND 
          MOBILE_NOTIFICATION_TYPE.NOTIFICATION_TYPE_ID=V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_TYPE_ID AND 
          V_MOBILE_PAGE_NOTIFICATION.NOFIFICATION_TITLE LIKE '%'||IP_KEYWORDS||'%';
  ELSE
    OPEN SELECT_RESULT FOR 
    SELECT 
    V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_ID,
    V_MOBILE_PAGE_NOTIFICATION.NOFIFICATION_TITLE,
    MOBILE_NOTIFICATION_TYPE.SCHOOL_NOTIFICATION_TYPE_NAME,
    V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_PUBLISH_DATE 
    FROM V_MOBILE_PAGE_NOTIFICATION,
         MOBILE_NOTIFICATION_TYPE 
    WHERE RN >= (V_CUR_PAGE + 1) AND
          RN <= (V_CUR_PAGE + IP_PAGE_SIZE) AND 
          MOBILE_NOTIFICATION_TYPE.NOTIFICATION_TYPE_ID=V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_TYPE_ID AND 
          V_MOBILE_PAGE_NOTIFICATION.NOTIFICATION_TYPE_ID=IP_NOTIFICATION_TYPE AND
          V_MOBILE_PAGE_NOTIFICATION.NOFIFICATION_TITLE LIKE '%'||IP_KEYWORDS||'%';
  END IF;      
        
END PROC_QUERY_PAGE_NOTIFICATION;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_TABLE_COUNT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_TABLE_COUNT" (
    TABLE_ID IN NUMBER,
    QUERY_RESULT OUT NUMBER 
)
AS
BEGIN
  CASE TABLE_ID
  WHEN 1 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM MOBILE_SCHOOL_NOTIFICATION;--通知
  WHEN 2 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_ACTIVITY;--活动
  WHEN 3 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_ENTERTAINMENT;--吃喝玩乐
  WHEN 4 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_ENTERTAINMENT_COMMENT;--吃喝玩乐评论
  WHEN 5 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM MOBILE_PART_TIME_JOB;--兼职
  WHEN 6 THEN
    SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_USER WHERE ALLOWADMINCP=0;
  --查询已注册用户，且未被锁定用户的总数
  WHEN 7 THEN 
    SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_USER 
    WHERE ALLOWADMINCP= 0 AND STATUS=0  AND REGSTATUS=0;
    --查询已注册用户,但被锁定的用户
  WHEN 8 THEN 
   SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_USER 
    WHERE ALLOWADMINCP= 0 AND STATUS=-1  AND REGSTATUS=0;
    
    --查询为注册用户
  WHEN 9 THEN 
     SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_USER 
    WHERE ALLOWADMINCP= 0 AND  REGSTATUS=-1;
  WHEN 10 THEN 
       SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_MESSAGE WHERE MSG_METHOD=1;
  --查询广告数据量
  WHEN 11 THEN
  SELECT COUNT(*) INTO QUERY_RESULT FROM  MOBILE_ADVERTISMENT;
  WHEN 12 THEN
  SELECT COUNT(*) INTO QUERY_RESULT FROM COMMON_FEEDBACK;
  WHEN 13 THEN
   SELECT COUNT(*) INTO QUERY_RESULT FROM CA_PLAY;
  ELSE
    QUERY_RESULT:=0;
  END CASE;
  
END PROC_QUERY_TABLE_COUNT;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_USERHEAD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_USERHEAD" (
USER_ID IN VARCHAR2,--用户名
QUERY_RESULT OUT SYS_REFCURSOR
)
AS        

BEGIN
  OPEN QUERY_RESULT 
  FOR
 SELECT 
    COMMON_USER_PROFILE.USERHEAD,
    COMMON_USER.NICKNAME
  FROM COMMON_USER_PROFILE,COMMON_USER
  WHERE 
    COMMON_USER.LOGIN_ID =USER_ID  AND COMMON_USER_PROFILE.LOGIN_ID=COMMON_USER.LOGIN_ID  ;

    
END PROC_QUERY_USERHEAD;

/
--------------------------------------------------------
--  DDL for Procedure PROC_QUERY_WEEK_BY_DATE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_QUERY_WEEK_BY_DATE" (
V_DATE IN  NVARCHAR2,
CURRENT_WEEK OUT NUMBER
)
AS

BEGIN
  CURRENT_WEEK:=-1;
  select WEEK_NO INTO CURRENT_WEEK from MOBILE_LESSON_ASSIGN WHERE to_date(BEGIN_DATE,'yyyy-mm-dd hh24:mi:ss') <= to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss') AND to_date(END_DATE,'yyyy-mm-dd hh24:mi:ss') >= to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss');
   
  
  DBMS_OUTPUT.PUT_LINE('CURRENT WEEK:'|| CURRENT_WEEK);
EXCEPTION
 WHEN OTHERS THEN
   CURRENT_WEEK:=888;
END PROC_QUERY_WEEK_BY_DATE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_RECORD_USER_STATUS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_RECORD_USER_STATUS" 
(
  IP_LOGIN_ID IN NVARCHAR2,
  IP_REG_IP IN VARCHAR2,
  IP_LAST_LOGIN_IP IN NVARCHAR2,
  IP_LAST_LOGIN_TIME IN VARCHAR2,
  IP_OPERATION_CODE IN NUMBER
)
AS
V_COUNT NUMBER;
BEGIN
  SELECT COUNT(*) INTO V_COUNT FROM COMMON_USER_STATUS WHERE LOGIN_ID=IP_LOGIN_ID;
  
  CASE 
  WHEN IP_OPERATION_CODE=1 THEN 
  
    IF V_COUNT > 0 THEN
      UPDATE COMMON_USER_STATUS SET LASTIP=IP_LAST_LOGIN_IP,LASTVISIT= IP_LAST_LOGIN_TIME,REGIP=IP_REG_IP  WHERE LOGIN_ID = IP_LOGIN_ID;
    ELSE 
      INSERT INTO 
        COMMON_USER_STATUS(LOGIN_ID,REGIP,LASTIP,LASTVISIT) 
      VALUES(IP_LOGIN_ID,IP_REG_IP,IP_LAST_LOGIN_IP,IP_LAST_LOGIN_TIME);
    END IF;
 
  WHEN IP_OPERATION_CODE=2 THEN 
  
    IF V_COUNT > 0 THEN
      UPDATE COMMON_USER_STATUS SET LASTIP=IP_LAST_LOGIN_IP,LASTVISIT= IP_LAST_LOGIN_TIME WHERE LOGIN_ID = IP_LOGIN_ID;
    ELSE 
      INSERT INTO 
        COMMON_USER_STATUS(LOGIN_ID,LASTIP,LASTVISIT) 
      VALUES(IP_LOGIN_ID,IP_LAST_LOGIN_IP,IP_LAST_LOGIN_TIME);
    END IF;
  
 
  END CASE;
END PROC_RECORD_USER_STATUS;

/
--------------------------------------------------------
--  DDL for Procedure PROC_SELECT_DETAIL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_SELECT_DETAIL" (
  IP_ID IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN OP_SELECT_RESULT FOR
    SELECT * FROM MOBILE_SCHOOL_NOTIFICATION WHERE NOTIFICATION_ID=IP_ID;

END PROC_SELECT_DETAIL;

/
--------------------------------------------------------
--  DDL for Procedure PROC_UPDATE_APP_CLICKS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_UPDATE_APP_CLICKS" (
  APP_ID IN NUMBER
)
AS
BEGIN
    UPDATE MOBILE_APP SET CLICKS=CLICKS+1 WHERE APP_MODEL_ID=APP_ID;
  
END PROC_UPDATE_APP_CLICKS;

/
--------------------------------------------------------
--  DDL for Procedure PROC_UPDATE_CLICKS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_UPDATE_CLICKS" (rID in number,last_clicks out number)
as
begin

  update COMMON_ENTERTAINMENT set CLICKS=CLICKS+1 where record_id=rID;
  select CLICKS into last_clicks from COMMON_ENTERTAINMENT where record_id=rID;
 end proc_update_clicks;

/
--------------------------------------------------------
--  DDL for Procedure PROC_UPDATE_CLIENT_PASSWORD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_UPDATE_CLIENT_PASSWORD" (
IP_USER_ID IN NVARCHAR2,
IP_PASSWORD IN NVARCHAR2,
OP_UPDATE_RESULT OUT NUMBER
)
AS
BEGIN
  OP_UPDATE_RESULT:=-1;
  
  UPDATE COMMON_USER 
  SET ENCRYPTPASSWORD = IP_PASSWORD 
  WHERE LOGIN_ID = IP_USER_ID;
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
  EXCEPTION 
    WHEN OTHERS THEN
    ROLLBACK;  
  OP_UPDATE_RESULT:=-1;
  
END PROC_UPDATE_CLIENT_PASSWORD;

/
--------------------------------------------------------
--  DDL for Procedure PROC_UPDATE_PASSWORD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_UPDATE_PASSWORD" (
IP_USER_ID IN NVARCHAR2,
IP_PASSWORD IN NVARCHAR2,
OP_UPDATE_RESULT OUT NUMBER
)
AS
BEGIN
  OP_UPDATE_RESULT:=-1;
  
  UPDATE WEB_ADMINCP_MEMBER 
  SET ENCYPTPASSWORD = IP_PASSWORD 
  WHERE ULOGIN_ID = IP_USER_ID;
  COMMIT;
  OP_UPDATE_RESULT:=1;
  
  EXCEPTION 
    WHEN OTHERS THEN
    ROLLBACK;  
  OP_UPDATE_RESULT:=-1;
  
END PROC_UPDATE_PASSWORD;

/
--------------------------------------------------------
--  DDL for Procedure PROC_UPDATE_USER_TAGS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_UPDATE_USER_TAGS" 
(
IP_LOGIN_ID IN NVARCHAR2,
IP_USER_TAGS IN NVARCHAR2
)
AS
 
BEGIN
 UPDATE COMMON_USER SET USER_TAGS = IP_USER_TAGS WHERE LOGIN_ID = IP_LOGIN_ID;
END PROC_UPDATE_USER_TAGS;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_APP_PUB
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_APP_PUB" (
  IP_APP_NAME IN NVARCHAR2,--应用名称
  IP_VERSION_NAME IN VARCHAR2,--应用版本名称
  IP_UPDATE_CONTENT IN NVARCHAR2,--更新内容
  IP_APPTYPE IN NUMBER,--应用类型:1:Android 2:IOS
  IP_APP_PATH IN NVARCHAR2,--应用所在路径
  IP_VERSION_CODE IN NUMBER,--应用版本号
  IP_PUB_DATE IN VARCHAR2,--发布日期
  IP_PUB_IP IN VARCHAR2,--发布IP
  IP_AUTHOR IN NVARCHAR2,--发布者
  IP_PUB_RESULT OUT NUMBER--发布结果：成功返回1.失败返回-1
)
AS

BEGIN
  IP_PUB_RESULT :=-1;
  INSERT INTO COMMON_APP_PUB(APPNAME,APPVERSIONNAME,UPDATECONTENT,APPTYPE,APP_PATH,APPVERSIONCODE,PUB_DATE,PUB_IP,AUTHOR)
  VALUES(IP_APP_NAME,IP_VERSION_NAME,IP_UPDATE_CONTENT,IP_APPTYPE,IP_APP_PATH,IP_VERSION_CODE,IP_PUB_DATE,IP_PUB_IP,IP_AUTHOR); 
  COMMIT;
  IP_PUB_RESULT:=1;
  
  EXCEPTION 
    WHEN OTHERS THEN
      ROLLBACK;
       IP_PUB_RESULT :=-1;
       
END PROC_WEB_APP_PUB;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_DELETE_PARTTIME
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_DELETE_PARTTIME" 
(
  IP_JOB_ID IN NUMBER,
  OP_DELETE_RESULT OUT NUMBER
)
AS

BEGIN
  OP_DELETE_RESULT := -1;
  DELETE FROM MOBILE_PART_TIME_JOB WHERE PARTIME_JOB_ID = IP_JOB_ID;
  COMMIT;
  OP_DELETE_RESULT := 1;
  
  
  EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    OP_DELETE_RESULT :=1;

END PROC_WEB_DELETE_PARTTIME;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_DETAIL_PARTTIMEJOB
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_DETAIL_PARTTIMEJOB" (
  IP_JOB_ID IN NUMBER,
  IP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN IP_SELECT_RESULT FOR 
  SELECT MOBILE_PART_TIME_JOB.*, MOBILE_PARTIME_JOB_TYPE.PARTIME_JOB_TYPE_NAME 
  FROM MOBILE_PART_TIME_JOB,MOBILE_PARTIME_JOB_TYPE 
  WHERE PARTIME_JOB_ID=IP_JOB_ID AND
    MOBILE_PARTIME_JOB_TYPE.JOB_TYPE_ID=MOBILE_PART_TIME_JOB.PARTIME_JOB_TYPE_ID
  ;
END PROC_WEB_DETAIL_PARTTIMEJOB;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_ENTERTAINMENT_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_ENTERTAINMENT_PAGE" (
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE  IN NUMBER,
    OP_SELECT_RESULT OUT SYS_REFCURSOR )
AS
  V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX       - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR SELECT * FROM
  (SELECT                          *
  FROM
    (SELECT T.*,
      ROWNUM RN
    FROM
      (SELECT *
       
      FROM COMMON_ENTERTAINMENT
    
      ORDER BY PUBLISH_DATE DESC
      ) T
    )
  ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
END PROC_WEB_ENTERTAINMENT_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_FEEDBACK_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_FEEDBACK_PAGE" (
    IP_PAGE_INDEX IN NUMBER,
    IP_PAGE_SIZE  IN NUMBER,
    OP_SELECT_RESULT OUT SYS_REFCURSOR )
AS
  V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX       - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR SELECT * FROM
  (SELECT                          *
  FROM
    (SELECT T.*,
      ROWNUM RN
    FROM
      (SELECT *
       
      FROM COMMON_FEEDBACK
    
      ORDER BY FB_DATE DESC
      ) T
    )
  ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
END PROC_WEB_FEEDBACK_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_PAGE_ACTIVITY
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_PAGE_ACTIVITY" 
(
  IP_PAGE_INDEX IN NUMBER,
  IP_PAGE_SIZE IN NUMBER,
  OP_SELECT_RESULT OUT SYS_REFCURSOR
)
AS
V_CUR_PAGE NUMBER;
BEGIN
--  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR
  SELECT * FROM ( 
  SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT * FROM COMMON_ACTIVITY ORDER BY PUB_DATE DESC ) T)
  )
  WHERE
  RN >= (V_CUR_PAGE + 1) AND 
  RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
  
END PROC_WEB_PAGE_ACTIVITY;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_PAGE_USER
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_PAGE_USER" (
    IP_PAGE_INDEX IN NUMBER,--索引
    IP_PAGE_SIZE  IN NUMBER,
    IP_REG_TYPE   IN NUMBER,--注册状态
    IP_LOCK_TYPE  IN NUMBER,--锁定状态
    IP_KEYWORDS IN NVARCHAR2,--关键字
    OP_SELECT_RESULT OUT SYS_REFCURSOR,
    OP_TYPE_COUNT OUT NUMBER )
AS
  V_CUR_PAGE NUMBER;
  T_IS_NULL NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX - 1) * IP_PAGE_SIZE;
  T_IS_NULL:=CA_CHECK_NULL(IP_KEYWORDS);
  ---选择已注册用户，并且未被锁定
  IF IP_REG_TYPE=0 AND IP_LOCK_TYPE = 0 THEN
    
    IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS                  = 0
        AND REGSTATUS               = 0
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS                  = 0
      AND REGSTATUS               = 0;
    ELSE 
         OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS                  = 0
        AND REGSTATUS               = 0
        AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS                  = 0
      AND REGSTATUS               = 0
      AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
    END IF;
    --已经注册，但是账号被锁定
  ELSIF IP_REG_TYPE=0 AND IP_LOCK_TYPE=-1 THEN
  
   IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS                  = -1
        AND REGSTATUS               = 0
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS                  = -1
      AND REGSTATUS               = 0;
    ELSE
        OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS                  = -1
        AND REGSTATUS               = 0
        AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS                  = -1
      AND REGSTATUS               = 0
      AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
      END IF;
    --查询未注册用户,被锁定用户
  ELSIF IP_REG_TYPE=                 -1 AND IP_LOCK_TYPE =-1 THEN
    IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
        AND STATUS                  = -1
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1
      AND STATUS                  = -1;
    ELSE
       OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
        AND STATUS                  = -1
        AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1
      AND STATUS                  = -1
      AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
    END IF;
    --未注册，账号未被锁定
  ELSIF IP_REG_TYPE=                 -1 AND IP_LOCK_TYPE =0 THEN
   IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
        AND STATUS                  = 0
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1
      AND STATUS                  = 0;
    ELSE
     OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
        AND STATUS                  = 0
        AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1
      AND STATUS                  = 0
       AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
    END IF;
  ELSIF IP_REG_TYPE             =1 AND IP_LOCK_TYPE=1 THEN --查询所有用户
   IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0;
    ELSE 
        OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
         AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
    END IF;
    --查询所有已注册用户,包含锁定和被锁定用户
     ELSIF IP_REG_TYPE=                 0 AND IP_LOCK_TYPE =1 THEN
      IF T_IS_NULL=1 THEN
        OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
        (SELECT T.*,
          ROWNUM RN
        FROM
          (SELECT COMMON_USER.*,
            COMMON_USER_PROFILE.TRUENAME
          FROM COMMON_USER,
            COMMON_USER_PROFILE
          WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
          AND COMMON_USER.ALLOWADMINCP=0
          AND REGSTATUS               = 0
          ORDER BY COMMON_USER.LOGIN_ID DESC
          ) T
        ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
        SELECT COUNT(             *)
        INTO OP_TYPE_COUNT
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = 0;
    ELSE
     OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
        (SELECT T.*,
          ROWNUM RN
        FROM
          (SELECT COMMON_USER.*,
            COMMON_USER_PROFILE.TRUENAME
          FROM COMMON_USER,
            COMMON_USER_PROFILE
          WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
          AND COMMON_USER.ALLOWADMINCP=0
          AND REGSTATUS               = 0
            AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
          ORDER BY COMMON_USER.LOGIN_ID DESC
          ) T
        ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
        SELECT COUNT(             *)
        INTO OP_TYPE_COUNT
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = 0
          AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ;
        
    END IF;
 ---未注册，所有状态
 ELSIF IP_REG_TYPE=                 -1 AND IP_LOCK_TYPE =1 THEN
  IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1;
    ELSE
     OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND REGSTATUS               = -1
         AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND REGSTATUS               = -1
       AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
      ;
    END IF;
    --所有使用中的账号
    ELSIF IP_REG_TYPE=                 1 AND IP_LOCK_TYPE =0 THEN
     IF T_IS_NULL=1 THEN
      OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS               = 0
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS               = 0;
    ELSE
     OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
      (SELECT T.*,
        ROWNUM RN
      FROM
        (SELECT COMMON_USER.*,
          COMMON_USER_PROFILE.TRUENAME
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS               = 0
          AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
        ORDER BY COMMON_USER.LOGIN_ID DESC
        ) T
      ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
      SELECT COUNT(             *)
      INTO OP_TYPE_COUNT
      FROM COMMON_USER,
        COMMON_USER_PROFILE
      WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
      AND COMMON_USER.ALLOWADMINCP=0
      AND STATUS               = 0
        AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          );
    END IF;
  --所有被锁定的用户
  ELSIF IP_REG_TYPE=                 1 AND IP_LOCK_TYPE =-1 THEN
      IF T_IS_NULL=1 THEN
    
        OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
        (SELECT T.*,
          ROWNUM RN
        FROM
          (SELECT COMMON_USER.*,
            COMMON_USER_PROFILE.TRUENAME
          FROM COMMON_USER,
            COMMON_USER_PROFILE
          WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
          AND COMMON_USER.ALLOWADMINCP=0
          AND STATUS               = -1
          ORDER BY COMMON_USER.LOGIN_ID DESC
          ) T
        ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
        SELECT COUNT(             *)
        INTO OP_TYPE_COUNT
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS               = -1;
    ELSE
        OPEN OP_SELECT_RESULT FOR SELECT DISTINCT * FROM
        (SELECT T.*,
          ROWNUM RN
        FROM
          (SELECT COMMON_USER.*,
            COMMON_USER_PROFILE.TRUENAME
          FROM COMMON_USER,
            COMMON_USER_PROFILE
          WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
          AND COMMON_USER.ALLOWADMINCP=0
          AND STATUS               = -1
           AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
          ORDER BY COMMON_USER.LOGIN_ID DESC
          ) T
        ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
        SELECT COUNT(             *)
        INTO OP_TYPE_COUNT
        FROM COMMON_USER,
          COMMON_USER_PROFILE
        WHERE COMMON_USER.LOGIN_ID  = COMMON_USER_PROFILE.LOGIN_ID
        AND COMMON_USER.ALLOWADMINCP=0
        AND STATUS               = -1
         AND 
          (
            COMMON_USER_PROFILE.TRUENAME LIKE '%'||IP_KEYWORDS||'%'
            OR
            COMMON_USER.LOGIN_ID LIKE '%'||IP_KEYWORDS||'%'
            OR
            IDNUMBER LIKE '%'||IP_KEYWORDS||'%'
          )
          
        ;
    END IF;
   END IF;
  
   
END PROC_WEB_PAGE_USER;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_READ_COUNT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_READ_COUNT" 
(
  IP_MEESSAGE_ID IN NUMBER,
  OP_READ_COUNT OUT NUMBER
)
AS
BEGIN
  SELECT COUNT(*) INTO OP_READ_COUNT FROM COMMON_MESSAGE_STATUS WHERE MSG_ID=IP_MEESSAGE_ID;
END PROC_WEB_READ_COUNT;

/
--------------------------------------------------------
--  DDL for Procedure PROC_WEB_READ_STATUS_PAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PROC_WEB_READ_STATUS_PAGE" (
    IP_PAGE_INDEX IN NUMBER,--页当前索引
    IP_PAGE_SIZE  IN NUMBER,--页大小
    IP_MSG_ID IN NUMBER,--消息ID
    OP_SELECT_RESULT OUT SYS_REFCURSOR )
AS
  V_CUR_PAGE NUMBER;
BEGIN
  V_CUR_PAGE:=(IP_PAGE_INDEX       - 1) * IP_PAGE_SIZE;
  OPEN OP_SELECT_RESULT FOR SELECT * FROM
  (SELECT                          *
  FROM
    (SELECT T.*,
      ROWNUM RN
    FROM
      (SELECT * FROM COMMON_MESSAGE_STATUS 
      WHERE MSG_ID =IP_MSG_ID 
      ORDER BY RECORD_ID DESC
      ) T
    )
  ) WHERE RN >= (V_CUR_PAGE + 1) AND RN <= (V_CUR_PAGE + IP_PAGE_SIZE);
END PROC_WEB_READ_STATUS_PAGE;

/
--------------------------------------------------------
--  DDL for Procedure PRO_INSERT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_INSERT" (
       deptno_in in dept.deptno%type,
       dname_in in dept.dname%type,
       loc_in in dept.loc%type
       )
as
  v_dept dept%rowtype;
begin
     begin
          insert into dept
          select deptno_in,dname_in,loc_in
          from dual;
          commit;
          dbms_output.put_line('inserting successed');
          exception
          when others then
               rollback;
     end;
     begin
          select deptno_in,dname_in,loc_in
          into v_dept from dual;
          dbms_output.put_line(
          'The data having been inserted.'||
          'deptno:'||v_dept.deptno||
          ',dname:'||v_dept.dname||
          ',loc:'||v_dept.loc
          );
     end;
end pro_insert;

/
--------------------------------------------------------
--  DDL for Procedure PRO_QUERYHOTSERACHBYTYPE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_QUERYHOTSERACHBYTYPE" (
HOT_TYPE IN NUMBER,--热点搜索类型
NEED_SIZE IN NUMBER,--所需记录条数
SEARCH_RESULT OUT SYS_REFCURSOR--数据放置到游标之中
)
AS
BEGIN
OPEN SEARCH_RESULT FOR
 SELECT * FROM (
    SELECT * FROM(
     SELECT T.*,ROWNUM RN FROM 
      (SELECT * FROM COMMON_HOT_SEARCH ORDER BY SEARCH_COUNT DESC)
     T
   )
 ) 
 WHERE RN <= NEED_SIZE;
 
END PRO_QUERYHOTSERACHBYTYPE;

/
--------------------------------------------------------
--  DDL for Procedure PRO_QUERY_LESSON
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_QUERY_LESSON" (
KEYWORD IN VARCHAR2,--关键字
QUERY_RESULT OUT sys_refcursor--返回游标类型
)
AS

T_COUNT NUMBER;--临时变量,用于存储拥有此关键字的行数
BEGIN
     
      --更新关键字数据表
      SELECT COUNT(*) INTO T_COUNT FROM COMMON_HOT_SEARCH WHERE SEARCH_CONTENT=KEYWORD;
      --判断是否存在,存在则搜索次数加一,否则创建新的行并将搜索次数置为1
      IF T_COUNT > 0 THEN--存在,字段+1
        UPDATE COMMON_HOT_SEARCH SET SEARCH_COUNT=SEARCH_COUNT+1 WHERE SEARCH_CONTENT=KEYWORD;
      ELSE--插入一条记录,并更新搜索次数
        INSERT INTO COMMON_HOT_SEARCH(SEARCH_TYPE,SEARCH_CONTENT,SEARCH_COUNT) VALUES(1,KEYWORD,1);
      END IF;
      
      --搜索数据库 
      OPEN QUERY_RESULT 
      FOR
      SELECT 
  
      MOBILE_LESSON.CLASSNO,--课程号
      MOBILE_LESSON.CLASSNAME,--课程名
      MOBILE_LESSON.CREDIT,--课程学分
      MOBILE_LESSON.PERIOD,--课程学时
      MOBILE_LESSON_PROP.NAME,--课程属性名称
      MOBILE_TEACHER.TEACHERNAME,--教师名称
      MOBILE_TEACHER.GENDER--教师性别
      FROM MOBILE_LESSON,MOBILE_T_SKB,MOBILE_LESSON_PROP,MOBILE_TEACHER
      WHERE 
      MOBILE_LESSON.CLASSNO = MOBILE_T_SKB.LESSON_NO 
      AND
      MOBILE_T_SKB.TEACHER_NO =  MOBILE_TEACHER.TEACHERNO
      AND 
      MOBILE_T_SKB.LESSON_TYPE = MOBILE_LESSON_PROP.RECORD_NO
      AND 
      CLASSNAME LIKE '%'||KEYWORD||'%';
END PRO_QUERY_LESSON;

/
--------------------------------------------------------
--  DDL for Procedure PRO_QUERY_REPRINT
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_QUERY_REPRINT" (
  REPRINT_ID IN NUMBER,--转载ID
  
  QUERY_RESULT OUT SYS_REFCURSOR--游标
)
AS
BEGIN
  OPEN QUERY_RESULT FOR 
    SELECT 
      COMMON_ENTERTAINMENT.USER_ID,--用户ID
      COMMON_ENTERTAINMENT.TITLE, --标题
      COMMON_ENTERTAINMENT.TEXT_CONTENT,--正文
      COMMON_ENTERTAINMENT.IMG_CONTENT,--图片内容
     
      COMMON_USER.NICKNAME,--用户昵称
      COMMON_USER_PROFILE.USERHEAD--用户头像
      FROM COMMON_ENTERTAINMENT,COMMON_USER,COMMON_USER_PROFILE
      WHERE 
        COMMON_ENTERTAINMENT.RECORD_ID = REPRINT_ID 
        AND 
        COMMON_ENTERTAINMENT.USER_ID=COMMON_USER.LOGIN_ID 
        AND 
        COMMON_USER_PROFILE.LOGIN_ID=COMMON_ENTERTAINMENT.USER_ID;
        

  
END PRO_QUERY_REPRINT;

/
--------------------------------------------------------
--  DDL for Procedure PRO_QUERY_REPRINT_V2
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_QUERY_REPRINT_V2" (
  REPRINT_ID IN NUMBER,--转载ID
  OP_SELECT_RESULT OUT NUMBER,
  QUERY_RESULT OUT SYS_REFCURSOR--游标
)
AS
BEGIN
  OPEN QUERY_RESULT FOR 
    SELECT 
      COMMON_ENTERTAINMENT.USER_ID,--用户ID
      COMMON_ENTERTAINMENT.TITLE, --标题
      COMMON_ENTERTAINMENT.TEXT_CONTENT,--正文
      COMMON_ENTERTAINMENT.IMG_CONTENT,--图片内容
     
      COMMON_USER.NICKNAME,--用户昵称
      COMMON_USER_PROFILE.USERHEAD--用户头像
      FROM COMMON_ENTERTAINMENT,COMMON_USER,COMMON_USER_PROFILE
      WHERE 
        COMMON_ENTERTAINMENT.RECORD_ID = REPRINT_ID 
        AND 
        COMMON_ENTERTAINMENT.USER_ID=COMMON_USER.LOGIN_ID 
        AND 
        COMMON_USER_PROFILE.LOGIN_ID=COMMON_ENTERTAINMENT.USER_ID;
        OP_SELECT_RESULT:=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  OP_SELECT_RESULT:=0;
WHEN OTHERS THEN
  OP_SELECT_RESULT:=-1;
END PRO_QUERY_REPRINT_V2;

/
--------------------------------------------------------
--  DDL for Procedure PRO_QUERY_USERHEAD
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."PRO_QUERY_USERHEAD" (
USER_ID IN VARCHAR2,--用户名
QUERY_RESULT OUT SYS_REFCURSOR
)
AS        

BEGIN
  --dbms_output.put_line('USERID='||user_id);
  OPEN QUERY_RESULT  FOR 
  SELECT 
    COMMON_USER_PROFILE.USERHEAD AS USERHEAD,
    COMMON_USER.NICKNAME AS NICKNAME
  FROM COMMON_USER_PROFILE,COMMON_USER
  WHERE 
    COMMON_USER_PROFILE.LOGIN_ID=COMMON_USER.LOGIN_ID AND COMMON_USER.LOGIN_ID=USER_ID;
  
END PRO_QUERY_USERHEAD;

/
--------------------------------------------------------
--  DDL for Procedure SEL_ONE_LESSON
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."SEL_ONE_LESSON" (
    IP_USER_ID IN NVARCHAR2,
    IP_LESSON_NO IN NVARCHAR2,
    OP_SEL_CODE OUT NUMBER,
    OP_SEL_RESULT OUT SYS_REFCURSOR
  )
  AS
    T_USER_ADDRESS NVARCHAR2(200);
    T_ADDRESS NVARCHAR2(200);
    T_USER_COLLEGE_NO NVARCHAR2(2);
    T_USER_MAJOR_NO NVARCHAR2(2);
    T_COLLEGE_NO NVARCHAR2(2);
    T_MAJOR_NO NVARCHAR2(2);
    T_USER_GRADE_NO NVARCHAR2(4);
    T_GRADE_NO NVARCHAR2(4);
    T_MARJOR_NICKNAME NVARCHAR2(20);
    T_REC CA_T_ONECLASS%ROWTYPE;
    T_CURSOR SYS_REFCURSOR;
    T_USER_STUNUMBER_LEN NUMBER(2) ;
    T_USER_NO NVARCHAR2(20);
  BEGIN
    OP_SEL_CODE :=-1;
    --OPEN OP_SEL_RESULT FOR
    
    --delete all datas
    delete from ca_t_oneclass;
    commit;
    
    INSERT INTO ca_t_oneclass(STUNO,CLASSNAME,NICKNAME,USERHEAD,SIGNATURE,GENDER)
      SELECT  
      distinct(V_MOBILE_LESSON.STUNO), 
      V_MOBILE_LESSON.CLASSNAME,
      common_user.NICKNAME,
      COMMON_USER_PROFILE.USERHEAD,
      COMMON_USER_PROFILE.SIGNATURE,
      COMMON_USER_PROFILE.GENDER
      FROM 
      V_MOBILE_LESSON LEFT JOIN common_user
      ON V_MOBILE_LESSON.STUNO = common_user.LOGIN_ID
      LEFT JOIN  COMMON_USER_PROFILE 
      ON common_user.LOGIN_ID = COMMON_USER_PROFILE.LOGIN_ID
      WHERE CLASSNO = IP_LESSON_NO AND common_user.REGSTATUS = 0;
    
--查询用户住址
    SELECT common_user_profile.ADDRESS INTO T_USER_ADDRESS FROM common_user_profile WHERE LOGIN_ID = IP_USER_ID;
    --查询用户学号和专业号
      IF LENGTH(IP_USER_ID) = 12 THEN
        BEGIN
          T_USER_COLLEGE_NO:=substr(IP_USER_ID,4,2);--学院号
          T_USER_MAJOR_NO:=substr(IP_USER_ID,6,2);--专业号
          T_USER_GRADE_NO:=substr(IP_USER_ID,0,4);--年级号
        --  T_USER_NO:=IP_USER_ID;
        END;
      elsif LENGTH(IP_USER_ID) = 4 THEN
        BEGIN
            T_USER_NO:=IP_USER_ID;
        END;
      END IF;
         
   
   
   --选择数据到游标
      OPEN T_CURSOR FOR 
        SELECT * FROM ca_t_oneclass;
       
       --打开游标
  
      LOOP
      --遍历游标
        
        FETCH T_CURSOR INTO T_REC;
          --没有数据则退出
          EXIT WHEN T_CURSOR%NOTFOUND;
          --数据处理
          --计算是否是老乡
          IF CA_CHECK_NULL(T_USER_ADDRESS)<>1 THEN--不为1
            BEGIN
               SELECT common_user_profile.ADDRESS INTO T_ADDRESS FROM common_user_profile WHERE LOGIN_ID = T_REC.STUNO;
               IF  T_USER_ADDRESS = T_ADDRESS THEN
                BEGIN
                  UPDATE CA_T_ONECLASS SET FELLOW = 1 WHERE  STUNO=T_REC.STUNO;
                  COMMIT;
              END;
               ELSIF   T_USER_ADDRESS <> T_ADDRESS THEN
                BEGIN
                  UPDATE CA_T_ONECLASS SET FELLOW = 0 WHERE  STUNO=T_REC.STUNO;
                  COMMIT;
                END;
               END IF; 
            END;
            
          END IF;
          
          
         --计算是否同年级
             IF LENGTH(T_REC.STUNO) = 12 THEN --普通学生,学号12位
                BEGIN
                   T_GRADE_NO := substr(T_REC.STUNO,0,4);--年级号
                  
                  --判断
                    IF T_GRADE_NO=T_USER_GRADE_NO THEN
                        BEGIN
                           UPDATE  ca_t_oneclass set onegrade = 1 where stuno = T_REC.STUNO;
                           commit;
                        END;
                    ELSIF  T_GRADE_NO <> T_USER_GRADE_NO THEN
                       BEGIN
                           UPDATE  ca_t_oneclass set onegrade = 0 where stuno = T_REC.STUNO;
                           commit;
                        END;
                    END IF;  
                 END;
             ELSIF LENGTH(T_REC.STUNO)=4 THEN--教师
        
                BEGIN
                  T_USER_NO:=IP_USER_ID;
                END;
          
            END IF;
          
          
          
          --计算是否是同专业
           IF LENGTH(T_REC.STUNO)=12 THEN --普通学生,学号12位
              BEGIN
                T_COLLEGE_NO:=substr(T_REC.STUNO,4,2);--学院号
                T_MAJOR_NO :=substr(T_REC.STUNO,6,2);--专业号
                
                IF T_COLLEGE_NO= T_USER_COLLEGE_NO AND  T_MAJOR_NO = T_USER_MAJOR_NO THEN
                      BEGIN
                        T_MARJOR_NICKNAME:='';
                        --  T_MARJOR_NICKNAME:=UPD_LOACTION.SEL_ONEMARJOR_NAME(T_GRADE_NO,T_COLLEGE_NO,T_MAJOR_NO);
                          UPDATE  ca_t_oneclass set  onemarjor  = T_MARJOR_NICKNAME  where stuno = T_REC.STUNO;
                          commit;
                       END;
                ELSE 
                  BEGIN
                       UPDATE  ca_t_oneclass set  onemarjor  = ''  where stuno = T_REC.STUNO;
                          commit;
                          END;
                END IF;
              END;
         ELSIF LENGTH(T_REC.STUNO)=4 THEN
             BEGIN
                  T_USER_NO:=IP_USER_ID;
            END;
         END IF;
      END LOOP;
      
      
     --关闭游标
     CLOSE T_CURSOR;
     
     
     --将查询数据选择到由表中
     OPEN OP_SEL_RESULT FOR
      SELECT * FROM ca_t_oneclass ;
     OP_SEL_CODE:=1;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      OP_SEL_CODE:=0;
    WHEN OTHERS THEN
      OP_SEL_CODE:=-1;
   
  END SEL_ONE_LESSON;
  
  

/
--------------------------------------------------------
--  DDL for Procedure TEST_MCAMPUS
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."TEST_MCAMPUS" 
 as
 begin
  null;
 end test_mcampus;

/
--------------------------------------------------------
--  DDL for Procedure TGR_SELECT_MESSSAGE_BY_ID
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."TGR_SELECT_MESSSAGE_BY_ID" (
	REQUEST_ID IN NUMBER,--请求ID
	SELECT_RESULT OUT SYS_REFCURSOR
)
AS 
BEGIN
	OPEN SELECT_RESULT
	FOR
		SELECT MSG_TITLE,MSG_CONTENT,PUB_DATE,PUB_AUTHOR,MESSAGE_NAME
		FROM COMMON_MESSAGE,COMMON_MESSAGE_TYPE
		WHERE RECORD_ID = REQUEST_ID AND COMMON_MESSAGE_TYPE.ID=COMMON_MESSAGE.MSG_TYPE;
	
END TGR_SELECT_MESSSAGE_BY_ID;

/
--------------------------------------------------------
--  DDL for Procedure T_MYPROCEDURE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "SYSTEM"."T_MYPROCEDURE" 
as 
myexception exception;   --定义异常类型变量
begin
  if 1>0 then
    raise myexception;  --出现异常时抛出异常
  end if;

  exception            
    when myexception then          --定义异常时处理的语句
      dbms_output.put_line('出现异常');

end;

/
